Meter dillema

[praetorianprefect]

I am designing a database to track utility usages, ie water and electricity. My problem is when a meter has been replaced how to track the usage. I was able to do the calculations in a query for the usage but when it came to a meter that has been replaced, it throws off the calculation. Let me illustrate:
Date Meter# Reads
1/1/12 - Meter A - 900
1/2/12 - Meter A - 950 (usage 50)
1/3/12 - Meter B (replacement) - 100 (usage -850)


1/4/12 - Meter B (replacement) - 120 (usage 20)

[June7]

Show the query SQL statement. Probably need another condition in the WHERE clause.

[praetorianprefect]

SELECT tblGas.Meter, tblGas.Customer, tblGas.ReadDate, tblGas.Read AS [Current], qryGasUsagePrep.Read AS Previous, IIf(IsNull([previous]) Or [previous]=0 Or [current]=0,[current]-[current],Int([current]-[previous])) AS [Usage], Round((((([usage]*[revcuft(x)])*[meterconst(x)])/[thermconst(/)])*([thermMult(x)]))*[percentage],2) AS Therm, tblGas.FY
FROM tblGas LEFT JOIN qryGasUsagePrep ON (tblGas.ReadDate = qryGasUsagePrep.qMonth) AND (tblGas.Meter = qryGasUsagePrep.Meter)
ORDER BY tblGas.Meter, tblGas.ReadDate;

[June7]

Probably need to see SQL for qryGasUsagePrep.

Do you want to provide database for analysis? Follow instructions at botton of my post or upload to fileshare site such as box.com and post link to file.

[praetorianprefect]

The table only has one meter serial number, my question is, if the meter gets replaced in the future, how will i accurately calculate the usage?

[June7]

Not the query technique I was expecting but it seems to work because there is only one reading per month. Why are there negative usages?

I added a couple of records for another meter for the same customer and changed the Usage calc to: [current]-Nz([previous],0).

Compare the two calcs side-by-side in the query. I don't think your original calc was giving correct output.

[praetorianprefect]

The reason for negative usages is due to the readings, i can only assume that the meter was replaced during that period. That is my problem, how would i accurately calculate the usage when a meter got replaced...

[June7]

A final reading from the meter getting replaced should be recorded. Then next reading for that customer should be with new meter. That could mean two readings for the same customer within one month. If the readings aren't taken at time of replacement and the change in meter number documented, I don't see how usage can be determined.

If the negative usages are indicating meter replacement, then that took place several times for that customer but the meter number was not changed. GIGO syndrome (garbage in garbage out). Customer gets a bonus.

Could those negatives actually be result of meter rolling over back to 0 after hitting its maximum readout display? How high can these meters accumulate?

Suggest have a condition that if the Previous value is greater than the Current value, subtract 0 from the Current. Or add the difference between the meter max and the previous reading.

[praetorianprefect]

Thank you, I was thinking of placing a condition as well. I will do some tests on adding the difference between the meter max and the previous read, thanks.

[praetorianprefect]

I was going over the calculation, I think I have found a solution. To compensate the meter change, this is what I have:
Old Formula
[current read]-[previous read]=[usage]

New Formula
IIF([current read]<[previous read],[current read]-0,[current read]-[previous read])

[June7]

Okay, but that doesn't deal with the rollover issue, if there is one.

[recyan]

In addition, just thinking ( do not know if it can practically happen):
1) A meter No "1" is installed at Customer "A".
2) After some time, problem occurs in Meter No "1" & it is removed for repair and replaced by Meter No "2" which is set at 0.
3) Meter No "1" is repaired & installed after resetting it to 0 at Customer "B".
4) Will it be useful to have data which identifies uniquely a Customer ID & Meter No ?
5) Will it be useful to have Date of Installation of a particular Meter No at a particular Customer ID ?
6) There could be a possibility, that, Meter No "1", after sometime, lands up again at Customer "A".

Boy, am I confused???

Thanks

[orange]

As the other responders have said/alluded,
You need to know the readings at the time of replacement.
Old meter reading and new meter reading.

I had this physically happen when new "smart meters" were installed.
My hydro bill calculations were correct.

They used
Old Meter PeriodStart reading, old meter reading at replacement DateTime, and
NewMeter StartReading at time of Replacement, new meter reading on Billing Date to do their calculations.


On a more practical basis, why are meters repaired and returned to a Customer? Why does the supplier not have a "pool" of "properly working meters" and replace defective meters as needed?

Good luck with your project.