Quote:
Originally Posted by kranezor
I have a record is
Date Batch No. data final
12/1/2007 THDA008261 76000
15/1/2007 THDA008262 74400
25/1/2007 THDA008313 158000
26/1/2007 THDA008314 54400
26/1/2007 THDA008315 55600
primary key is Batch No.
I want a new coloum of the query to display like this
Date Batch No. vis final Diff data
12/1/2007 THDA008261 10 0
15/1/2007 THDA008262 50 40
25/1/2007 THDA008313 20 -30
26/1/2007 THDA008314 60 40
26/1/2007 THDA008315 20 -40
Regards,
kran
Hi, kran.
First and for most, that is a kind of task a spreadsheet (like Excel) could do more effectively.
- Using spreadsheet approach you may export the dataset (appropriately ordered) to Excel and add formula to calculate differences.
- Though it is possible with a query (table self-join on batch number from a first alias is the next one to that from a second alias), it could take a long time for the query to run. As far as I could recall a much more light join logic (number comparisson instead of number containing string comparisson) took several hours to run on 50k records table. How many records do you have in the table?
Second point is about how batch numbers are supposed to be compared:
- Does a greater number in a batch number always mean the batch number is greater?
- Does batch number always start from "THDA"?
- Does batch number always start from fix-width symbol combination?
- Any other points from you concerning batch number comparisson.
Regards,
Fish
