Query Custom Calculation Help

[Djbell]

Hi All

I have a table that has two fields Week No and Amount. I have created a Query that uses these two fields, but I would also like two custom calculations in here, one which calculates the weekly difference and one that calculates the total difference. so for example.

Week No | Amount | Weekly Chg | Total Chg
1 | 165 | 0 | 0
2 | 163 | -2 | -2
3 | 160 | -3 | -5
4 | 161 | 1 | -4
5 | 157 | -4 | -8

I have tried a few things but just cant figure out how to use figures in previous reords for my calculations.

Any help is appreciated

Regards

Douglas Bell

 

[BoxHead]

Maybe try:

Weekly Chg: Iif([Week No] = 1,0,[Amount] - DLookUp("[Amount]","tblName","[Week No] = " & [Week No] - 1))

Total Chg: IIF([Week No] = 1,0,[Amount] - DLookUp("[Amount]","tblName","[Week No] = 1"))


HTH





When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.