Access Calculations based on a previuos rows data

 

 

 

 

I have a table with meter readings. Every day we record a reading. to find out the difference we have to subtract the previuos reading from the current reading. Normally one does arithmetic with the same row of data. When you want to look at the previous row it becomes a little bit more complicated. The following explains how to do this in Access:

Access Screenshot

In a query paste the following in the Field : See above

Delta1: [meter_9600456_1500]-DLookUp("meter_9600456_1500","meter readings","Date = #" & [Date]-1 & "#")

The syntax for the query is as follows:
Delta1 is the name of the column in which we will return the result

Tthe first meter_9600456_1500] is the reading in the current row

Tthe DLookup looks up the table named "meter readings", it looks for the date minus one, that is the previous row.

"Date = #" & [Date]-1 & "#" does the date calculation. the "#" and "&" are to convert it into a string so you can perform the query. [Date] is the current row date.

Finally "meter_9600456_1500" tells Access where to look for a value when the date is current date minus one.