subtracting consecutive rows 1

[FederalProgrammer]

Any ideas?
is there a way to subtract consecutive rows of a table using SQL on Access???
ie, newField.row(i) = row(i) - row(i-1)

Obviously I need to get two copies of the table... and some how use the IIF function (maybe!!) i don't know...
any help is much apreciated!!

 

[checkai]

go to the Access forum...

 

[donutman]

Do you want it to be a running total where the new value in row(i) is then used in the computation of row(i+1)? Or is this a static calculation of the differences between successive values? Is there a guarnteed sort order (i.e. is the sort order unique)? It would be terrific if the order happened to be a primary key integer field.
-Karl

 

[FederalProgrammer]

Static...
The new field is simple the diference between row(i) and row(i+1); The new field.row(i) is not involved in the calculation at all...

Here's a subset of excatly what I have:
TimeStep Value
0 400
1 300
2 200
3 100

What I need is a query returning the following result:
TimeStep calculated value
0 NULL
1 100
2 100
3 100

cheers!

 

[donutman]

Select M2.Value-M1.Value from MyTable M1 left join MyTable M2
on M1.TimeStep=M2.TimeStep+1 order by M1.TimeStep

BTW, you returned positives when your 1st post would have returned negatives.
-Karl

 

[bertrandkis]

FederalProgrammer,
You can convert this code into vba code if you are working in access but I did it in SQL.This code will do the trick for you. I assume that you want to capture the difference in a new table,I called your existing table #temptest and the new table #temptest2:
declare @c int
declare @val int
set @c=0
while @c<=(select max(TimeStep) from #temptest)
begin
set @val=((select Value from #temptest where TimeStep=@c) - (select Value from #temptest where TimeStep=@c-1))
Insert #temptest2 values(@c,@val)
set @c=@c+1
end
So basically you need 2 variables and a while loop.
Enjoy
Bertrandkis.

 

[FederalProgrammer]

Thanx a million donut dude!!!
So simple and yet!! wow, i can't believe I didn't think of that!!