calculating

[bregci]

calculating differences between two values

I have a problem. Can anybody help me?
My problem is:

I have a querie something like that. I need a formula witch will calculate field_x ??

id_field..|.. date_of ..|..counter.....|....field_x
-------------------------------------------------------
1..........2.2.2004.........A(number).......A
2..........1.3.2004.........B (number).....B-A
3..........4.4.2004.........C..................C-B
4..........2.5.2004.........D..................D-C
................................E.................. E-D
. . . .
. . . .

Thanks

 

[FoFa]

You could try some thing like the following, but you might have to play around with it a bit since it was off the top of my head.

Select T1.id_field, T1.date_of, T1.counter, iif((select min(T2.[id_field]) from Tbl T2)=T1.[id_field],T1.[counter],(select T3.[counter] from Tbl T3 where T3.[id_field] = (T1.id_field - 1)) - T1.counter) as field_x from Tbl T1

 

[bregci]

You could try some thing like the following, but you might have to play around with it a bit since it was off the top of my head.

Select T1.id_field, T1.date_of, T1.counter, iif((select min(T2.[id_field]) from Tbl T2)=T1.[id_field],T1.[counter],(select T3.[counter] from Tbl T3 where T3.[id_field] = (T1.id_field - 1)) - T1.counter) as field_x from Tbl T1

What is T2 and T3 in this formula?

I have only one table.

 

[FoFa]

What is T2 and T3 in this formula?

I have only one table.

Since I was referencing the same table multiple times in the subqueries, I like to Alias the tables so I KNOW which one I am refering to. This is done in the FROM Tbl T1 code (this is assigning an alias name of T1 to this instance of Tbl). SQL requires it in certain conditions like the select T3.[counter] from Tbl T3 where T3.[id_field] = (T1.id_field - 1), part, how would it know which id_field to use if I didn't tell it T3 or T1