Meter Roll Over

  • Thread starter Thread starter Meter Reader
  • Start date Start date
M

Meter Reader

I am creating an application to calculate the water usage
at 230 locations. At each location we have a meter with
a mechanical 3-digit odometer that is read on a weekly
basis. I have created an input form for the meter reader
to input the weekly meter reading data into a table. The
table has 3 fields, MeterID, DateRead and
Meter_Reading. I have created a query to calculate the
difference between the current meter reading and the
previous meter reading. The formula I used to produce the
previous reading is as follows:

PrevReading: DMax("[Meter_Reading]","Query:
MeterReadings","[MeterID] = " & [MeterID] & " AND
[DateRead] < #" & [DateRead] & "#")

HERE'S MY DILEMMA!!??
When the 3-digit odometer on the meter rolls over (ie 999
to 000), the formula produces the Maximum Meter_Reading
for the MeterID. For example:

MeterID DateRead Meter_Reading PrevReading
001 3/1/04 950 900
001 3/7/04 5 950
001 3/14/04 60 950

Since the previous reading is incorrect my calculation
for meter usage for that period is also incorrect.

My goal is to create an input form where the meter reader
only has to input the current meter reading. To resolve
this problem, I know I can create an input form that
prompts the meter reader to input both the current meter
reading and the previous meter reading but that creates
an additional chance for a data input error.

ANY SUGGESTIONS?
 
Just don't use "max" for the meter reading...but just grab the *last* date.

PrevReading: (select top 1 [Meter_reading] from MeterReadings as tq
where tq.MeterId = MetreReasdings.MeterId
and tq.dateRead < MetreReadings.dateRead
order by desc tq.dateRead)

That way, you only rely on the last date...and don't use the "max".

Also, note how I used a table "alias" name of "tq". This is done as to NOT
to confuse which table you are referring to.

Note that the above query MUST return only ONE record to work. If you will
have more then one meter reading on the same day for a given meter id..then
you need to add a autonumber id..and order by that...

order by desc tq.dateRead, by desc ID)
 
Meter Reader said:
I am creating an application to calculate the water usage
at 230 locations. At each location we have a meter with
a mechanical 3-digit odometer that is read on a weekly
basis. I have created an input form for the meter reader
to input the weekly meter reading data into a table. The
table has 3 fields, MeterID, DateRead and
Meter_Reading. I have created a query to calculate the
difference between the current meter reading and the
previous meter reading. The formula I used to produce the
previous reading is as follows:

PrevReading: DMax("[Meter_Reading]","Query:
MeterReadings","[MeterID] = " & [MeterID] & " AND
[DateRead] < #" & [DateRead] & "#")

HERE'S MY DILEMMA!!??
When the 3-digit odometer on the meter rolls over (ie 999
to 000), the formula produces the Maximum Meter_Reading
for the MeterID. For example:

MeterID DateRead Meter_Reading PrevReading
001 3/1/04 950 900
001 3/7/04 5 950
001 3/14/04 60 950

Since the previous reading is incorrect my calculation
for meter usage for that period is also incorrect.

My goal is to create an input form where the meter reader
only has to input the current meter reading. To resolve
this problem, I know I can create an input form that
prompts the meter reader to input both the current meter
reading and the previous meter reading but that creates
an additional chance for a data input error.

ANY SUGGESTIONS?
?? ??
Click to expand...
 
Meter Reader said:
I am creating an application to calculate the water usage
at 230 locations. At each location we have a meter with
a mechanical 3-digit odometer that is read on a weekly
basis. I have created an input form for the meter reader
to input the weekly meter reading data into a table. The
table has 3 fields, MeterID, DateRead and
Meter_Reading. I have created a query to calculate the
difference between the current meter reading and the
previous meter reading. The formula I used to produce the
previous reading is as follows:

PrevReading: DMax("[Meter_Reading]","Query:
MeterReadings","[MeterID] = " & [MeterID] & " AND
[DateRead] < #" & [DateRead] & "#")

HERE'S MY DILEMMA!!??
When the 3-digit odometer on the meter rolls over (ie 999
to 000), the formula produces the Maximum Meter_Reading
for the MeterID. For example:

MeterID DateRead Meter_Reading PrevReading
001 3/1/04 950 900
001 3/7/04 5 950
001 3/14/04 60 950

Since the previous reading is incorrect my calculation
for meter usage for that period is also incorrect.

My goal is to create an input form where the meter reader
only has to input the current meter reading. To resolve
this problem, I know I can create an input form that
prompts the meter reader to input both the current meter
reading and the previous meter reading but that creates
an additional chance for a data input error.

ANY SUGGESTIONS?
hao
Click to expand...
 
I have created the following expession based on your
recommendation:

PrevReadingTest: (Select Top 1 [Meter_Reading] from
Query_MeterReadings as tq where tq.Order =
Query_MeterReadings.Order And tq.DateRead <
Query_MeterReadings.DateRead Order by Desc tq.DateRead)

An error message occurs when I run the query:

"Syntax Error in Order By Clause".

I have never used the Order By clause in an expression so
I don't know what the desc represents. If I remove the
desc, I don't receive the message, however the output is
incorrect. It provides the first meter reading as the
output. Please explain the purpose of the "desc" and how
do I write the expression correctly.

Thanks So much!!
Meter Reader
 
Meter Reader said:
I have created the following expession based on your
recommendation:

PrevReadingTest: (Select Top 1 [Meter_Reading] from
Query_MeterReadings as tq where tq.Order =
Query_MeterReadings.Order And tq.DateRead <
Query_MeterReadings.DateRead Order by Desc tq.DateRead)

An error message occurs when I run the query:

"Syntax Error in Order By Clause".

I have never used the Order By clause in an expression so
I don't know what the desc represents. If I remove the
desc, I don't receive the message, however the output is
incorrect. It provides the first meter reading as the
output. Please explain the purpose of the "desc" and how
do I write the expression correctly.

Thanks So much!!
Meter Reader
Click to expand...

Albert made a small mistake. Change "Order by Desc tq.DateRead" to
"Order by tq.DateRead Desc". The DESC (or desc, or desc) keyword in an
ORDER BY clause means, "sort into descending sequence on this field".
 
It worked........Thanks!!
-----Original Message-----
I have created the following expession based on your
recommendation:

PrevReadingTest: (Select Top 1 [Meter_Reading] from
Query_MeterReadings as tq where tq.Order =
Query_MeterReadings.Order And tq.DateRead <
Query_MeterReadings.DateRead Order by Desc tq.DateRead)

An error message occurs when I run the query:

"Syntax Error in Order By Clause".

I have never used the Order By clause in an expression so
I don't know what the desc represents. If I remove the
desc, I don't receive the message, however the output is
incorrect. It provides the first meter reading as the
output. Please explain the purpose of the "desc" and how
do I write the expression correctly.

Thanks So much!!
Meter Reader
Click to expand...

Albert made a small mistake. Change "Order by Desc tq.DateRead" to
"Order by tq.DateRead Desc". The DESC (or desc, or desc) keyword in an
ORDER BY clause means, "sort into descending sequence on this field".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Back
Top