Current meter reading query

Hello directorz,

Is there no field which identifies the meter?

Pete

try this,
you will need a meterID for the query

SELECT tblMeterRead.MeterID, Max(tblMeterRead.MeterRead) AS Current_MeterReading, Max(tblMeterRead.ReadDate) AS MaxOfReadDate
FROM tblMeterRead
WHERE (((tblMeterRead.MeterRead)>0))
GROUP BY tblMeterRead.MeterID;

Hi Pete and Capricorn1,

I've uploaded a sample for a better visual. Please see geocities.com. Username is directorz2002.  Password is ticket.

Just a few notes: The MeterMax field is a user defined field. Different meters have different maximum readings ie; 9,999 hours or 999,999 miles etc. The RolloverCount uses this field to determine a rollover. Because RolloverCount is an attribute of the meter, I think I'm still in compliance with normalization.

Capricorn1...if I associate a rollover count with a current reading, as you have it, I think the calcs will not be accurate. Could you elaborate.

Try searching EE - body:"meter reading" - I got 18 hits and I think your answer is in there.

Your code is calling the maximum meter read as the current meter read. But, if the meter reset to zero, all bets are off.

So somehow, I  need to incorporate the fact that if the current meter read is less than the last meter read, the meter has rolled over and the rollover count equals the current value of the rollover count field +1. Let's say the meter rolls once.  The current meter reading will then be the sum of  the current reading + (MeterMax * 1). If it rolls twice, the current reading will be sum of  the current reading + (MeterMax * 2). You may see the dilemma.

GRayL,
I did a search and did not find too much, but I appreciate it.

Directorz

<Your code is calling the maximum meter read as the current meter read. But, if the meter reset to zero, all bets are off>
No.

that is why this clause

WHERE (((tblMeterRead.MeterRead)>0))  

Capricorn1,
I may not be seeing this right, please correct me if I am wrong. Your code selects the highest reading, greater then zero and makes it the current reading. If this is true, I don't understand how a rollover is accurately handled.

Oh no, here I go again. Let's say we're nearing the meter maximum of 9999. So, on October 1, I read 9980. October 2nd   I read 9995. Time goes on, lah-lah-lah-lah (with background music) and on October 5th, I read 10. Will your code result with 9995 or 10?

I think we're on the same page. I think the rollover needs to be handled by VB. Shanesuebsahakarn started me off with this concept. If the current meter reading is LESS THAN the last reading, then a rollover has occurred .

At the time of a rollover, I need to add "1" to the current value of the rollover field. In other words,
"the rollover count equals the current value of the rollover count field +1."

 Let's say the meter rolls once.  The current meter reading will then be the sum of the current reading + (MeterMax * 1). If it rolls twice, the current reading will be sum of  the current reading + (MeterMax * 2)....metermax being the maximum or highest reading the meter can show without rolling over to zero.

I think this will be a separate question, presented as a continuation of this one.

or if you have a RollOver field Update it to

RollOver=Rollover+1
everytime the difference between the last reading and the previous one is less than 0; meaning negative value (10-9995)

Shanesuebsahakarn,
I will followup with this. By keeping a 'rollovercount' field as a field in the table, am I  violating 3NF normalization rules. Should this be done at the form level. Just want to make sure when I start a new question...

There are circumstances when you need to denormalise. This is probably one of them, given the nature of the data. You probably could calculate the number of rollovers, but it would be very, very tricky using SQL - you'd more than likely need VBA to do it in any case.

Capricorn1,
I'm showing yours as accepted but I need to split with shanesuebsahakarn...could have worked also

Thank you

Directorz  

As a continuation of this question - this may be a better way - I'll add 700 points. Using the previous tables and arrangement,  Anyway, I'm hung up on a rollover count. The concept is to get a rollover count and use it in determinining an 'actual' reading. Using a query, I'm thinking, as shanesuebsahakarn suggested in another thread, that the logic is if the current meter reading is LESS THAN the last meter reading, a rollover has occurred

The RolloverCount field value is increased by 1 each time this occurs. The RolloverCount field value is then multiplied by MeterMax and added to MeterRead. Jeez, I'm trying to be as clear as possible without confusing everyone.  

Anyone want to give this one a shot?