Sum of difference

[Malachy39]

Column A is a date field
Column B is a currency field.

How do I query the difference between record X and record X+1.

For Instance.
Date Currency
1/1 $310
1/2 $432
1/3 $500

The query should show on the second line $122 and on the third line $168.

Thanks for you help.

 

[Jon K]

Assuming the fields Date and Currency are in table tblCurrency and there are no duplicate dates in the table, you can achieve what you desired with two queries.

qry1:-
SELECT DCount("*","tblCurrency","Date < #" & [Date] & "#") AS Rank, *
FROM tblCurrency
ORDER BY Date;

qry2:-
SELECT Date, Currency,
Currency - DLookup("Currency", "qry1", "Rank= " & [Rank]-1 & "") AS Difference
FROM Qry1;


Run qry2.