subtracting in an access report

[chrishut]

I have an access database for meter readings. I use a form to set up a query
then preview a report.

My report displays the said meter readings between the given dates. I cant
seem to get my head around subtracting the first reading from the last to
give an actual usage figure. Can someone please help?

 

[Allen Browne]

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

 

[chrishut]

Thanks Allen,

I've used the DLookup() to Display the the Title of the meter on my report,
which solved another issue that I had. Brilliant!
I still can't seem to apply that to achieve my metered usage. I could have
any number of readings in the field on the report due to the fact that it
displays all the readings taken between the preselected dates. Is there any
way I can find the difference between the first and last record.

 

[Allen Browne]

Could you use DMax() to get the highest previous value for the meter,
instead of DLookup()?

That should work unless you have to cope with cases where the meter was
replaced with a new one, so the current reading was reset to zero and is
therfore lower than the highest previous reading. If you have to handle that
kind of case, you need to retrieve the meter reading value from the maximum
date before the current one. You could use this extended version of
DLookup() to do that:
http://allenbrowne.com/ser-42.html

As long as you don't minde read-only results, a subquery will be much more
efficient. You would type something like this into the Field row in your
query:
PriorReading: (SELECT MeterReading FROM tblMeter AS Dupe
WHERE Dupe.MeterID = tblMeter.MeterID
AND Dupe.ReadingDate < tblMeter.ReadingDate
ORDER BY Dupe.ReadingDate DESC, Dupe.MeterID DESC)

More on subqueries:
http://support.microsoft.com/?id=209066

 

[chrishut]

I'll have a go with that , thank you Allen

Could you use DMax() to get the highest previous value for the meter,
instead of DLookup()?

That should work unless you have to cope with cases where the meter was
replaced with a new one, so the current reading was reset to zero and is
therfore lower than the highest previous reading. If you have to handle that
kind of case, you need to retrieve the meter reading value from the maximum
date before the current one. You could use this extended version of
DLookup() to do that:
http://allenbrowne.com/ser-42.html

As long as you don't minde read-only results, a subquery will be much more
efficient. You would type something like this into the Field row in your
query:
PriorReading: (SELECT MeterReading FROM tblMeter AS Dupe
WHERE Dupe.MeterID = tblMeter.MeterID
AND Dupe.ReadingDate < tblMeter.ReadingDate
ORDER BY Dupe.ReadingDate DESC, Dupe.MeterID DESC)

More on subqueries:
http://support.microsoft.com/?id=209066