retrieve "previous" reading

[desertdirk]

HI
fairly new to all this and am working on a database that will generate electrical billing. I need to figure out how to retrieve an electric meter reading from the previous month so that I can then subtract it from the current reading. So far I have:

Table Meter Readings:
ID (pk)
MeterReadDate
SiteID (fk)
MeterReading

I am not sure how to approach this, ie what type of a search criteria I can use to retrieve this info.

Thanks for any help
DD

 

[DrSimon]

I always find temporal relationships like this difficult in Access or any relational database and would be interested if anyone else has better ideas than mine.
The way I approach this is to use multiple queries in a cascade.
a) Get the latest date i.e. maximum for the siteID
b) Run a query for all dates less than found in (a) for that siteID
c) Get maximum date from (b) for the siteID
d) In one query join original table to (a) [By SiteID and date] and a copy of the original table to (c) [By SiteID and date] and then join the original table and copy by SiteID. The readings from the table and the copy will be from the current and previous value.
If this doesn't make sense, let me know and I'll give more details.
I'd be delighted to know an easier way.
Simon Rouse

 

[johnnymac43]

This would be simpler from a processing POV and more logical from a business POV if the latest reading was placed in a separate table until billing was done, and then moved to the main table. A simple extension to this concept would be to also keep a copy of (or a pointer to) the most recent billed reading in another table.

Cheers

John

 

[DrSimon]

I like your first suggestion John, but of course it only works if you have control over the database structure. I often have to work with situations when the database is being controlled by other applications.
I'm not so sure about the second suggestion as I have the feeling that it could give rise to maintenance problems.
Simon Rouse

 

[DrSimon]

I've just remembered another approach I've used to this sort of problem. Rank the dates by each siteID and then select the record with a rank 1 less than the one selected.

 

[desertdirk]

Simon, John
Thanks for these ideas. Simon I liked the idea of using a Max sorting (actually sum I guess) and thought I would try that on the meter reading grouped by siteID, but that wouldn't work if the meter "rolled over" to 00000. I guess I will try using the date as you suggested. The only issue I see is having to use the full date (dd/mm/yy) instead of just the month and year as these people have been doing. Sometimes a meter is read on the first of the month, then again on the last of the month to get that month's billing.

John I have been considering using a "temp" table to hold the current reading. So that would mean creating an update query, then once the billing was done, an append query to the tblMeterReading table?

I have to take in to account the fact that the number of meters changes from month to month (this is for an RV Campground and they have a mixture of long-term tenants and month-to-month tenants)
Dirk