Solved

Meter rollover

Posted on 2004-09-30
22
1,602 Views
Last Modified: 2006-11-17
   This question is a continuation of Q_21149251.html. I've uploaded a sample of the db along with some data to geocities.com.  Go thru file manager, username is directorz2002, password is ticket.

I need to set up a 'rollover count' that keeps track of any meter rollovers (re-sets to zero). Here's the thought process:

If the current meter reading is less than the one before it, a rollover has occurred. The RolloverCount field value then needs to be adjusted by "1". Each additional rollover is handles the same way. The ultimate goal of this is to be able to reflect a true meter reading. For example, if the maximum number that a  meter can show is 9999 before it re-sets to zero - and it  does - I need to show 9999 + the current reading. The formula would be someting like [(LastMeterRead) + (MeterMax * RolloverCount)]. In terms of SQL or VB, how is this best handled and can anyone assist.

Thank you

Directorz

      
0
Comment
Question by:directorz
  • 13
  • 8
22 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12189178
check this for a starter, need some modification to suit your need

How to Compare a Field to a Field in a Prior Record
http://support.microsoft.com/?kbid=208953
0
 
LVL 5

Expert Comment

by:jmacmicking
ID: 12189583
I took the liberty of uploading my version of the fix to your geocities site.  It adds a "RolledOver" field to your meter readings to indicate when a value has rolled over from the previous reading.  The code that marks these records is in Module1; I provided a macro to run the code.  There are now three queries to determine the actual meter reading, the first counts the number of roll overs per meter, the second grabs the most recent reading and the final query does the math.  If you want more details on how it works let me know.

0
 

Author Comment

by:directorz
ID: 12190363
Capricorn1,
I'm still looking at your suggestion.

jmacmicking,
Yours is interesting. In running some samples, it does not appear to handle multiple rollovers. Also, if an a user inadvertently enters an incorrect number that triggers a rollover count, correcting the error does not seem to modify the table. I need to look at this more.
0
 
LVL 5

Expert Comment

by:jmacmicking
ID: 12190404
I took a look at the problem; it's more a problem with the macro then the code.  Somehow the macro doesn't actually have anything in the RunCode section, so running the macro doesn't do anything.  Don't know when or how I managed to do that.  Running the code directly works fine and handles multiple roll-overs.  If you link the RunCode in the macro back to the function it should be fine.
0
 

Author Comment

by:directorz
ID: 12191205
jmacmicking,
Could you elaborate more on the details of how this works?

Thank you
0
 
LVL 5

Expert Comment

by:jmacmicking
ID: 12191612
The code in Module1 does most of the work; it first opens the meter readings table and sorts it by MeterID and then by Date.  It then loops through each record and checks to make sure that the MeterID matches the previous MeterID and, if it does, that the meter reading is greater then the previous reading.  If the MeterID doesn't match it just resets the last reading to the current reading and goes to the next record.  If the MeterID matches but the reading is less then the previous reading it marks the record as RolledOver.
I
If you need details on something else (or if I misunderstood your question) please let me know.

I'm assuming you were able to fix my fubar with the macro; if you weren't I can upload the fixed version.  Just let me know.
0
 

Author Comment

by:directorz
ID: 12191837
OK, here's what I did. I cleared the entries in the MeterRead table. I entered 4 values in the MeterRead field...9000, 9500, 9999 and 10 in that order. I then ran QryCurrentMeterReading and the CurrentReading field value is 10 - correct. But, when I run QryMeterTotals, the rollovercount field is blank. Am I missing something
0
 

Author Comment

by:directorz
ID: 12191884
Could you upload the fix...
0
 
LVL 5

Expert Comment

by:jmacmicking
ID: 12192185
The fixed version has been updated (same name as before).  Just run the macro after you add meter readings and it should work fine.
0
 

Author Comment

by:directorz
ID: 12192733
I see. I'm running some test data...looks good so far. So the macro can be triggered by an event on a form
0
 

Author Comment

by:directorz
ID: 12192874
Is the rollovercount field still needed in tblmeters?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:directorz
ID: 12193084
Also, InitialMeterRead does not appear to be included in the total calculation. This would be important...
0
 

Author Comment

by:directorz
ID: 12193098
disregard that
0
 

Author Comment

by:directorz
ID: 12193126
I believe the initial reading should be subtracted from the total. This would accomodate a user who reuses a meter in another vehicle
0
 
LVL 5

Expert Comment

by:jmacmicking
ID: 12193128
The macro could be triggered by a form event or by the command line (if you want to run it once per night, for instance).  You can also run the code directly.  However, the code isn't optimized for running after adding individual records.  It will work fine but performs more work then necessary.  

If you're checking records as they're added I'd recommend looking up the most recent reading (you've already got a query that does this) and comparing the readings and the date before the record gets updated.  Then set the RolledOver field right then.  This won't make much difference while you only have 100 or so records; heck on most machines it won't make any appreciable difference with 1000 or so.  If you have more then that though it could make a  difference.  If you want help with it just post the form you're using into the database on geocities and I'll take a look at it.

No, the rollovercount field isn't needed any more.  

I used the basic formula you outlined in the question ( [(LastMeterRead) + (MeterMax * RolloverCount)]).  If you want initial reading calculated in some how it shouldn't be hard to add, just post the formula you actually want me to use :).
0
 
LVL 5

Accepted Solution

by:
jmacmicking earned 500 total points
ID: 12193220
Change the SQL statement in QryMeterTotals to this:

SELECT TblMeter.MeterID, IIf(IsNull([initialmeterread]),0,[initialmeterread]) AS InitialMeterReading, QryCurrentMeterReading.MostRecentRead, QryCurrentMeterReading.CurrentReading, QryRolloversPerMeter.RollOverCount, TblMeter.MeterMax, IIf(IsNull([RollOverCount]),[CurrentReading]-[InitialMeterReading],[CurrentReading]+([RollOverCount]*[MeterMax])-[InitialMeterReading]) AS TotalReading
FROM (QryCurrentMeterReading INNER JOIN TblMeter ON QryCurrentMeterReading.MeterID = TblMeter.MeterID) LEFT JOIN QryRolloversPerMeter ON TblMeter.MeterID = QryRolloversPerMeter.Ref_MeterID
ORDER BY TblMeter.MeterID;

The IIf statements are necessary in case the InitialMeterRead field is empty.  
0
 

Author Comment

by:directorz
ID: 12193315
Yes, this was an oversight on my part. Hey, I've got to run out and hopefully modify it this evening. I appreciate your time and will up the points for your expending what could have been a good fishing day.
0
 

Author Comment

by:directorz
ID: 12199105
Good morning jmacmicking,
This set up really works well and solves the problem! I have not gotten to the form yet but I would like to run this as records are added on an update event. Yes, I would be concerned with performance as the potential for a significant number of records is there. Could you elaborate more on optimization
0
 
LVL 5

Expert Comment

by:jmacmicking
ID: 12201078
As it turns out, it's a good thing you asked.  When I went back to check on everything I found a problem where entries added out of date order would cause the last entry to be picked incorrectly.  The query picked the correct date but matched the wrong reading with it.  To avoid this I split the query that picks the last entry into two queries; one to find the last date and one to pick the last reading on that date.  This cured the problem.

I added a basic form to the db to give you an idea what I'm talking about.  I also changed the macro so it removes roll-over records before running (to clear incorrect entries).

When you click the Add button on the form it checks the last record for that meter.  
If the last recorded reading occured before the reading that is being added the form checks to see if the new meter reading is less then the last reading.  If it is, the record is automatically marked as rolled-over.
If the new reading occured before the last stored reading a MsgBox will pop up informing the user that a full refresh of the rollovers should be performed (basically, the macro should be run).  It allows them to have the form run the refresh, ignore the warning (perhaps to run the macro later) or cancel the save.  If they choose to have the form run the refresh it only effects records related to the current meter; it runs as soon as the record is saved.  If you have a lot of meters this will be faster then running the macro (which effects resets every meter).

The form never marks readings that occured before the last reading as rolled-over.  This prevents two records being marked for the same roll-over.  I'm guessing that it would be better in the short term to have accurate meter counts then to have the correct record marked for the roll-over.  Running the macro will clear up the inconsistencies--it should probably be run once a week or so, depending on how much data is entered and how frequently readings are entered out of order.  

One note; the form currently does no data entry checking before it performs the last entry checks.  If the data entered doesn't meet the table's requirements (for instance, a required field is left blank) the last entry check will still be performed.  The user won't get an error until the form actually tries to save the record after the check.  Ideally, the form should check the data before starting the last entry check--you'll have to add that yourself though once you have all the fields in place and have decided which are required.
0
 

Author Comment

by:directorz
ID: 12202823
I see the error in running sample data here. You’re talking about the QryCurrentMeterReading not showing the correct current reading. I have the updated version and it appears to be corrected. I’ve got a followup for you. I’m not sure if this can be tied in here or if it’s a separate issue altogether. In the db, each vehicle or piece of equipment can have many meters and those meters can be of many types ie; hourmeter, odometer etc. It is possible to have a meter fail and need to be replaced. In this event, a new meter of the same type, say an hourmeter, would become the primary meter and the replaced meter would become ‘secondary’ to it, so to speak, or ‘inactive’.  I could not delete the meter as I would lose readings and records associated with it. I’ve included a ‘Primary’ checkbox field in TblMeter for this purpose. So, would the current arrangement handle this aspect of the scheme. I've got to run shortly but I will try to do a sample form which may be a better visual. Also, if you respond to the “Points“  posting earlier, I can award them.
0
 
LVL 5

Expert Comment

by:jmacmicking
ID: 12203226
The current totalling system is per-meter.  It shouldn't be too hard to extend that to per-vehicle-per-meter type.  In essence, you'd have to tie the per-meter totals to the vehicle and then sum all the totals for that vehicle.  Active or inactive wouldn't really matter to the totals; it would only really apply when you're adding new meter readings.
However, since the historical data is fixed it may be better store the historical total with either the active meter or the vehicle itself.  If you still need the individual readings for other reasons (or just as a paper trail) it could be moved to a "historical" table.  Since all the records on the meter readings table have to be summed each time to produce the current reading, moving data that can't possibly change to a read-only table makes sense.  Just add a field to the meter table for HistoricalTotal or some such and add it in when calculating the meter total.  You could then setup a form to gather the information for the new meter (initial reading would seem to be the one most likely to change) that automatically totals the old meter readings, puts the total in the new meter's HistoricalReading field and moves the old records to the history table.

I'm not sure what you mean by the "Points" posting earlier; if you have a link please post it and I'll respond.  
0
 

Author Comment

by:directorz
ID: 12205402
I'm not sure how to do a link, but if you go to Q_21152287.html you will see the post.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

10 Experts available now in Live!

Get 1:1 Help Now