Table order breaking.

Printable View

Jan 27th, 2012, 06:31 AM
vijaysinghvns

Table order breaking.

Dear Members,
I am new to forum. I have a problem regarding order by clause. Suppose my clients cash data is as following for the date 24/01/12:

24/01/12:
1. Cash paid to Mr. A 5000.00 Payment Voucher
2. Cash paid to Mr.B 2000.00 Payment Voucher
3. Cash paid to Mr.C 2500.00 Payment Voucher
4. Cash recd from Mr.D 6000.00 Receipt Voucher
5. Cash recd from Mr.E 8000.00 Receipt Voucher
6. Cash paid to Mr.F 4000.00 Payment Voucher

My client is not maintaining voucher number. I have a field DRCR containing “D” for payment & “C” for receipts. My output for cash should look like following:
24/01/12:
4. Cash recd from Mr.D 6000.00 Receipt Voucher
5. Cash recd from Mr.E 8000.00 Receipt Voucher
1. Cash paid to Mr. A 5000.00 Payment Voucher
2. Cash paid to Mr.B 2000.00 Payment Voucher
3. Cash paid to Mr.C 2500.00 Payment Voucher
6. Cash paid to Mr.F 4000.00 Payment Voucher
Order should be receipts first then payments in their respective order.

I am passing the following query:

mSqlQry = "select trndata.`vchrdate`, trndata.`vchrno`, trndata.`acctcode`," & _
" trndata.`narr`, trndata.`amount`, trndata.`drcr`, acctmaster.`acctdesc`," & _
"acctmaster.`station`, acctmaster.`acctcode` " & _
"from `trndata` trndata inner join `acctmaster` acctmaster on trndata.`acctcode` = acctmaster.`acctcode`" & _
"where format(vchrdate,'yyyy/mm/dd') between format('" & mDateFrom & "','yyyy/mm/dd') and format('" & mDateTo & "','yyyy/mm/dd') and trncode = '" & mTrnCode & "' order by vchrdate, drcr"
TempTrnDataRs.CursorType = adOpenKeyset
TempTrnDataRs.LockType = adLockOptimistic
TempTrnDataRs.Open mSqlQry, gConnect

But after ordering my results are as below:

5. Cash recd from Mr.E 8000.00 Receipt Voucher
4. Cash recd from Mr.D 6000.00 Receipt Voucher
6. Cash paid to Mr.F 4000.00 Payment Voucher
3. Cash paid to Mr.C 2500.00 Payment Voucher
2. Cash paid to Mr.B 2000.00 Payment Voucher
1. Cash paid to Mr. A 5000.00 Payment Voucher

I dont know where I am wrong... please suggest me...

regards
Vijay
Jan 27th, 2012, 06:55 AM
Hack

Re: Table order breaking.

Moved From The VB6 Codebank (which is for posting code you would like to share with others rather than asking questions :) )
Jan 27th, 2012, 07:31 AM
techgnome

Re: Table order breaking.

What database are you using?

-tg
Jan 27th, 2012, 07:34 AM
vijaysinghvns

Re: Table order breaking.

Quote:

Originally Posted by techgnome

What database are you using?

-tg

Access2003
Jan 28th, 2012, 12:34 AM
vijaysinghvns

Re: Table order breaking.

Quote:

Originally Posted by vijaysinghvns

Access2003

no reply.... :confused: :confused: :confused:
Jan 29th, 2012, 08:45 PM
techgnome

Re: Table order breaking.

If it was SQL Server, I might be able to help... sadly I don't have a lot of experience with Access...

but... you need to sort by Date, drcr, then the voucher number (vchrno)... I think that's what you're missing from your sort...

-tg
Jan 30th, 2012, 12:23 AM
vijaysinghvns

Re: Table order breaking.

Quote:

Originally Posted by techgnome

If it was SQL Server, I might be able to help... sadly I don't have a lot of experience with Access...

but... you need to sort by Date, drcr, then the voucher number (vchrno)... I think that's what you're missing from your sort...

-tg

Dear TG,
I have tried the same with sql server but results was the same as above... :confused::confused::confused:

rgds
Vijay
Jan 30th, 2012, 05:22 AM
szlamany

Re: Table order breaking.

You said the VOUCHER NUMBER was not being maintained.

If that really means that field is not in your table - then how would you expect to ORDER BY that?
Jan 31st, 2012, 12:27 AM
vijaysinghvns

Re: Table order breaking.

Quote:

Originally Posted by szlamany

You said the VOUCHER NUMBER was not being maintained.

If that really means that field is not in your table - then how would you expect to ORDER BY that?

I am not expecting order by on voucher number but I want that order by should not disturb the original position of a record.... i.e. if I am ordering on voucher date then it should appear in the order they made i.e. very first voucher at the top and last at the bottom in cash book for a particular date...but order by on date is disturbing the position of record and giving results in reversed order....i.e. last voucher at the top in cash book and first voucher at bottom.... :(:(:(
Jan 31st, 2012, 04:57 AM
szlamany

Re: Table order breaking.

Then you put a transaction # into the table.

If you have a business requirement to keep rows in some particular order then you put a field in the table to make that happen.

One of the rules of making tables is to have a PRIMARY KEY which is unique (only occuring on a SINGLE ROW in the table) - what field is that for you here??
Jan 31st, 2012, 05:30 AM
vijaysinghvns

Re: Table order breaking.

Quote:

Originally Posted by szlamany

Then you put a transaction # into the table.

If you have a business requirement to keep rows in some particular order then you put a field in the table to make that happen.

One of the rules of making tables is to have a PRIMARY KEY which is unique (only occuring on a SINGLE ROW in the table) - what field is that for you here??

It is not possible to create an unique ID for each record... bcz if you have added some fake entries after few days/month on a particular back date then it will be easly tracable by income tax authoroties that we have passed fake entries and will create problem to my client....
However I have planned to pass a temporary numbering by selecting whole data before passing above query and will add that number at the end as below:
" order by vchrdate, drcr, unino"

rgds
Jan 31st, 2012, 05:59 AM
szlamany

Re: Table order breaking.

That is honestly one of the most absurd things I've ever heard.

Actually - most auditors REQUIRE a transaction number - so they can see GAPS.

GAPS aren't illegal - they simply need to be "explained" by the staff.

You never answered my question - what is the PRIMARY KEY - unique and usually UNCHANGEABLE value that a rows has?

How do you UPDATE a row - what is your WHERE clause??
Jan 31st, 2012, 06:54 AM
techgnome

Re: Table order breaking.

Oh my gawd...

I'm with szlamany on this one... that is one of the most craziest things I've ever heard... and I thought I'd heard it all. AND one of my primary functions is building & customizing General Ledger (GL) systems for clients... If it were me, I'd be ripping it out and rebuilding it. You have to, have to, HAVE TO, have tracability... if you don't have a transaction number of any kind, then how can you possibly track the transactions as they move into, through, and out of the system? As much as the system that my client has bothers me and makes me nervious... what you're proposing scares the bejebies out of me.
If you need to order by entry then you need to track the date AND time the item was added to the table, and make that part of your order by.

-tg
Jan 31st, 2012, 07:30 AM
FunkyDexter

Re: Table order breaking.

I agree with TG and Szlamany that a transaction id would be a good idea (and I've personally never worked on a system without one) and deliberately designing an application to be opaque to the tax authorities REALLY doesn't sound like a good idea to me... but that's not really the point of your question. The point of your question is that you want a certain order and can't get it, so lets have a look at that.

First of all this statement contains a fundamental miss-understanding:-

Quote:

but I want that order by should not disturb the original position of a record....

Records in a database do not have any order. Of course, they're stored in some physical order on the disk but they have no logical order. The DBMS does not guarantee to return you records in any order other than that which you specify in the query. Never assume that the DBMS is going to return you records in the order you expect unless you ask for it - that's not part of the deal.

OK, so that means you need to specify the order fully in your query. From your requirements so far your order is actually:-
Order By Date asc, DRCR asc, Time asc

TG already asked the important question, are you recording the time an item was saved? Because without that you simply don't know what order your records were created in, you only know what date they were created
Jan 31st, 2012, 07:34 AM
szlamany

Re: Table order breaking.

Just to throw a little issue in that I've run into...

MS SQL server (don't know about ACCESS) sometimes returns an order that seems like the "original" entry order even without specifying it.

Then all of sudden something changes - record count in table goes above 10,000 (or 100,000 - whatever - the number is not the point) - or related tables have X-more records then before...

And all of a sudden the ORDER you were getting back a year ago becomes random as such - and you realize that you needed to put my fields in the ORDER BY to really guarantee your results...
Jan 31st, 2012, 08:17 AM
techgnome

Re: Table order breaking.

One more point of order, indexes will also affect the natural ordering of records... especially clustered ones.

-tg
Jan 31st, 2012, 04:17 PM
szlamany

Re: Table order breaking.

Quote:

Originally Posted by techgnome

One more point of order, indexes will also affect the natural ordering of records... especially clustered ones.

-tg

Until some magic size is reached and then all hell breaks lose...

Bottom line - if you want the order to be a certain way then SPECIFY ALL FIELDS in the ORDER BY statement...
Feb 1st, 2012, 12:22 AM
vijaysinghvns

Re: Table order breaking.

Quote:

Originally Posted by szlamany

Until some magic size is reached and then all hell breaks lose...

Bottom line - if you want the order to be a certain way then SPECIFY ALL FIELDS in the ORDER BY statement...

Many many thanks to all friends who gave thier experts view to my thread.... in fact i'm still not famileir with SQL/Access database i was using vfp databse in the past and I did not found any difficulty there but now I understand that there is no so called ORIGINAL :D data row in SQL/Access database...
once again thank you very much....

regards
VIjay