design problem: meter overflow

[rvWoody]

I essence my data fields are a date and a corresponding meter value. Unfortunately the meter only allows for 4 digits, so sooner or later it will overflow. The aim is to calculate
average use over an extended period of time. Is there any best practice to store and handle this kind of data?

If I just store the meter's face value, then the application has to scan sequentially all data to know how many overflows occurred during the interval in order to perform the desired calculation.

If on the other hand, through a data entry interface, I store the adjusted difference between to readings, then I have to sum all values for that interval.

There seems to be no free lunch?

PS: for some meters, the problem gets more complicated because they are battery fed. If the battery is replaced they restart at zero. For these I can not assume that when they go from 7543 to 123 they actually went from 7543 to 9999 and then to 123.

[kentg]

The way you are storing the data is fine, it's getting the results you need help with.

To get the average of valid entries you first need to exclude invalid entries. So start by contructing a query with a "where Reading_1 > Reading_2" so your query will look something like -

"Select Avg(Reading_2 - Reading_1) as AverageDifference From Readings Where Reading_1 > Reading_2"

[johnhutchison]

Is the one table holding readings for multiple meters? If so, is there a field to indicate what meter it came from?

How is the data being inputted? I am wondering if you could use an after update event to find the previous reading for the same meter, then try to calculate and store the consumption.

How will you ever know if the Battery has been replaced? Can you see if the calculated consumption is outside a reasonable range? And then what? Exclude the reading from future calculations? Or replace it with something else?

[rvWoody]

Is the one table holding readings for multiple meters? If so, is there a field to indicate what meter it came from?

There is a Meters table and a Readings table. The Readings table holds data for several meters. The latter are refered to by a Secondary Key.

How is the data being inputted? I am wondering if you could use an after update event to find the previous reading for the same meter, then try to calculate and store the consumption.

Through an Access Data Entry form. I settled for two data values for the readings table:
1. The actual reading, inputted through the data entry form and kept for audit reasons.
2. A calculated total field used for further statistical calculations
When a meter is selected, I fetch the last actual reading for that meter. If it is greater than the current value an overflow is assumed and the calculated total is adjusted accordingly.
As a blasphemy to good database design, I will probably replicate the last reading into the meter table to speed up processing.

How will you ever know if the Battery has been replaced? Can you see if the calculated consumption is outside a reasonable range? And then what? Exclude the reading from future calculations? Or replace it with something else?

That's a though one. Normally the digits on the reader fade away gradually as the battery looses power. So the battery can be replaced before it goes entirely down. A reading can still be done.
I have to provide for an additional field (checkbox) in the data entry form (and as well in the readings table(yes:no)) to indicate this event. I then can process this reading.
I'm fully aware that I'm dependent on human correctness, but I found no other alternative.

[rvWoody]

"Select Avg(Reading_2 - Reading_1) as AverageDifference From Readings Where Reading_1 > Reading_2"

I'm not sure what you mean by Reading_1 and Reading_2 as I only have one field with the actual readings in the table.

[kentg]

Using the ID for the meter then I'd suggest that, at the time of entry, you find the previous entry for the meter and either record the last reading in the same record, in which case the above SQL will work, or calculate the difference. Then, if the difference falls within a reasonable range, it is stored to use to calculate the average.

For example -

strSQL = "SELECT * FROM tblReadings WHERE Meter_ID = " & lngMeterID & " ORDER BY Date DESC"
Set rstReading = currentdb.openrecordset(strSQL, dbOpenSnapShot)
with rstReading
if not .eof then
if !Reading > Me.txtReading then ' it the last reading is greater than the current reading then it may have overflowed or been reset
' the example assumes that the reading is only 4 digits
intDifference = 10,000 + Me.txtReading - !Reading ' this will give you the actual difference if there was an overflow or a number that is probably too big if a reset occurred
else
intDifference = Me.txtReading - !Reading '
end if
end if

if intDifference < intReasonableDifference then
me.txtDifference = intDifference
else
me.txtDifference = Null ' it was not a valid reading
end if
end with

You should now have stored only differences when a reset did not occur which you can use to average.
When you use Avg in a query, Null values will be ignored.

Do you think that will work?

[rvWoody]

[color blue]kentg:[/color] the logic I use is approximately the same, except that I store (next to the actual meter readings) the calculated absolute meter readings (which have no digit number limits) and
you store the adjusted differences between two readings. As explained in a reply to [color blue]J. Hutchison[/color], I store the last meter reading (face value and absolute value) in also in the Meter table.
I hereby avoid the ORDER BY clause each time I enter a new reading (at the expense of an additional database access for the Meter table).

Both methods have advantages and inconveniences:

1. Differences:
Advantage: Easy to implement and the average is calculated within a single SQL statement.
Inconvenience: The average of the differences is only meaningful if the interval between two recording dates is fixed, which in practice is not guaranteed.
2. Absolute recording:
Advantage: Calculating a daily average is rather straightforward (in principle)
Inconvenience: I takes some VBA code to do it (at least I didn't find a way within SQL)

I will elaborate on the latter inconvenience: Given any two days (d1,d2) I can use the following SQL statement to create a recordset with all necessary data:

[color blue]SELECT RecordDate AS RD,AbsReadingValue As ARV FROM tblMeterValues WHERE RecordDate IN (d1,d2) ORDER BY RecordDate.[/color]

This should result in a two row recordset. The (daily) average is then: (ARV2-ARV1)/(RD2-RD1)
Can this calculation be performed within SQL?

[kentg]

Becuase you need to calculate the number of days between the two records, then no you cannot get a resutl with just one line of SQL.

But it seems you've got the result you need with calculation you provide in your last reply.

Are you where you need to be or do you still have a problem?

[rvWoody]

[color blue]kentg[/color] I can live with the solution, just wondered if this was a good (or even best practices) approach. Seems like there are little alternatives. I appreciate for your time and effort.

[kentg]

There are always multiple solutions to any problem, the best is usually the simplest as simple solutions are easier to understand when you go back to them months later.

The most time consuming part of maintenance or further modification is generally working out what's going on so simple and well commented solutions will save time and money in the long run.

In this case, calculating the daily average, at the time of entering a reading, and storing it in the same record would be my choice of best practice.

This is going to be the easiest structure for reporting.