Calculated field values between records

[MW]

I have records of daily meter readings for water use. I need to calculate "daily reading" value in record 2 from the "daily reading" value in record 1 to determine net use for day 2, etc. Seems simple and is a breeze in Excel, but I need help in doing it in Access.

Any suggestions?

Thanks

 

[jimbrooking]

You could try something like this. Suppose your table is "tblReadings", it's primary key is an autonumber field called "ID", and it contains another field called "Meter" containing the meter reading:

SELECT IIf([ID]=1,0,[Meter]-DLookUp("Meter","tblReadings","ID=" & ([ID]-1))) AS Diff FROM tblReadings;

Define a new query in Design View, Close the Add Table dialog, switch to SQL view and paste the above SQL into the query (replacing what was there). Switch to Datasheet view and see what you get.

This assumes the smallest index in your table is 1, and that no indices are missing.

Excel it ain't, because you can only "see" one record at a time.

Jim

 

[Pat Hartman]

Use a report. Using a DLookup() or other domain function in a query is extremely slow and if you have more than a few thousand rows, the query could be painfully slow.