Solved

Current meter reading query

Posted on 2004-09-29
15
512 Views
Last Modified: 2012-05-05
Good morning,

As part of a db that uses meters (odometer, hourmeter, etc.), I have a table as follows:

TblMeterRead
MeterReadID - pk
MeterRead
ReadDate

I'm attempting to set up a 'rollover count' that keeps track of any meter rollovers (re-sets to zero) and in order to do so I need a "current meter reading" field. Logic would dictate that the current reading is the last meter read but I need some assistance in wording SQL to set up this field in a query.

Thank you

Directorz
0
Comment
Question by:directorz
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 12178930
Hello directorz,

Is there no field which identifies the meter?

Pete
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12179132
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;

0
 

Author Comment

by:directorz
ID: 12179566
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.
0
 
LVL 44

Expert Comment

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

Author Comment

by:directorz
ID: 12180686
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12181059
<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))  
0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 250 total points
ID: 12181164
If you have a meter ID field, this will return the latest meter reading:

SELECT * FROM tblMeterRead WHERE ReadDate=(SELECT Max(A.ReadDate) FROM tblMeterRead As A WHERE A.MeterID=tblMeterRead.MeterID)

Simply join this query to the meter table and then add the rollover field*rollover value to the current meter reading.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:directorz
ID: 12181912
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?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
ID: 12182087
oh, it will yield 9995, i think i totally misunderstood your problem.
this will give the last reading

SELECT tblMeterRead.MeterID, Max(tblMeterRead.ReadDate) AS MaxOfReadDate, Last(tblMeterRead.MeterRead) AS LastOfMeterRead
FROM tblMeterRead
GROUP BY tblMeterRead.MeterID


how do you want to account for the rollover?
0
 

Author Comment

by:directorz
ID: 12182314
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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12182518
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)
0
 

Author Comment

by:directorz
ID: 12182526
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...
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12183688
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.
0
 

Author Comment

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

Thank you

Directorz  
0
 

Author Comment

by:directorz
ID: 12184399
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?
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now