How I get query for Using divide each of 2 rows

[kranezor]

I have a data like this,
Data1 Data2
35 30
25 10
40 20

I want 3rd column for display like this,
Data1 Data2 Data3
35 30 (default=0)
25 10 (30-10)
40 20 (10-20 )
.. .. [(1st row of Data2)-(2nd row of Data2)]

how I get the query or VB for resolve this proble
thank you

[FishVal]

Hello.

How a row could be recognized as a previous one?
Or rephrasing the question: do you have a primary key field in the table or at least any unique field?

Regards,
Fish.

[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

[FishVal]

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