Calculate the difference between records

[Guest]

Hi,

I have a table that has an autonum primary key and fields of date, meterID,
reading and volume. What I would like is for the volume field to be the
result of a calculated field of the latest reading for a particular meterID
minus the previous meter reading for that same meterID (think water use). I
assume this can be done via an update query (?) if the user fills in the
meterID, date and reading, but I am getting rather confused.

Hopefully someone can shed some light on this. Any help greatly appreciated.

Thanks

 

[Jack MacDonald]

I have used two approaches - one faster to execute, but more complex
(or impossible) to set up. The other is easier to understand, but
slower to execute.

Fast execution approach - this involves creating a "self-join" in
which the table is joined to itself, but that record #n is related to
record #n-1. How? that's the trick. Your database structure must
include some information that will reliablly relate one record to the
previous one. You can use the autonumber PK **IF** you can safely
assume that sequential records will have sequential PK's -- not always
a safe assumption. You create the query in the conventional manner in
the grid by adding the table twice and joining the PK fields. Then
switch to SQL view and manually chage the "=" relationship in the JOIN
clause from table1.PKfield = Table2.PKfield to table1.PKfield =
table2.PKfield-1

Second approach is to add a computed field to your single-table query
using the DMax() function to find the record that has the latest date
that is LESS than the value of the current record.

[aircode]

SELECT PKField, DateField, VolDifference: VolField -
DMax("volField","VolumeTableName","DateField<" & [DateField])

Easier to construct than the first approach, but it slows down
significantly when there are many records in the database.

Hi,

I have a table that has an autonum primary key and fields of date, meterID,
reading and volume. What I would like is for the volume field to be the
result of a calculated field of the latest reading for a particular meterID
minus the previous meter reading for that same meterID (think water use). I
assume this can be done via an update query (?) if the user fills in the
meterID, date and reading, but I am getting rather confused.

Hopefully someone can shed some light on this. Any help greatly appreciated.

Thanks

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security