?
Solved

Current meter reading query

Posted on 2004-09-29
15
Medium Priority
?
533 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 1000 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
 

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses

765 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