Latest meter reading

I am trying to query the latest meter reading on 17 pieces of equipment listed in a table.

The table consists of 5 fields (RecordID, Asset#, HourMeterReading, Date, Hours). Each month the information is added with each of the fields.

The bottom line is that I would like to most recent "HourMeterReading" for each of the 17 assets in the database. I have tried the max value on Date but have not been successful in retrieving the correct information. I cannot use the max value on the machines because some of them have been replaced and began with new (0) meter readings. As such, the meters in the new assets will have a lower meter reading than the original equipment it replaced.

I am thanking you, in advance, for any assistance.

--
Dan Johnson

Hi,
If I understand you correctly, this should work:

SELECT MyDate , tblMeterReading.Asset,HourMeterReading
FROM tblMeterReading INNER Join (SELECT Max(tblMeterReading.MyDate) AS
MaxOfMyDate, tblMeterReading.Asset
FROM tblMeterReading
GROUP BY tblMeterReading.Asset) t1 ON tblMeterReading.MyDate =
t1.MaxOfMyDate AND tblMeterReading.Asset = t1.asset;

I removed the # from the field name and I changed Date (which is a reserved
word) to MyDate
Substitute your table name for tblMeterReading

--

HTH
Dan Artuso, Access MVP

"Dan Johnson" wrote in message
...
I am trying to query the latest meter reading on 17 pieces of equipment
listed in a table.

The table consists of 5 fields (RecordID, Asset#, HourMeterReading, Date,
Hours). Each month the information is added with each of the fields.

The bottom line is that I would like to most recent "HourMeterReading" for
each of the 17 assets in the database. I have tried the max value on Date
but have not been successful in retrieving the correct information. I cannot
use the max value on the machines because some of them have been replaced
and began with new (0) meter readings. As such, the meters in the new assets
will have a lower meter reading than the original equipment it replaced.

I am thanking you, in advance, for any assistance.

--
Dan Johnson

This worked. I changed the Table and Field Names to match mine and it worked
like a champ. Thank you very much for the help!!!!

--
Dan Johnson


"dan artuso" wrote in message
...
Hi,
If I understand you correctly, this should work:

SELECT MyDate , tblMeterReading.Asset,HourMeterReading
FROM tblMeterReading INNER Join (SELECT Max(tblMeterReading.MyDate) AS
MaxOfMyDate, tblMeterReading.Asset
FROM tblMeterReading
GROUP BY tblMeterReading.Asset) t1 ON tblMeterReading.MyDate =
t1.MaxOfMyDate AND tblMeterReading.Asset = t1.asset;

I removed the # from the field name and I changed Date (which is a
reserved word) to MyDate
Substitute your table name for tblMeterReading

--

HTH
Dan Artuso, Access MVP

"Dan Johnson" wrote in message
...
I am trying to query the latest meter reading on 17 pieces of equipment
listed in a table.

The table consists of 5 fields (RecordID, Asset#, HourMeterReading, Date,
Hours). Each month the information is added with each of the fields.

The bottom line is that I would like to most recent "HourMeterReading" for
each of the 17 assets in the database. I have tried the max value on Date
but have not been successful in retrieving the correct information. I
cannot use the max value on the machines because some of them have been
replaced and began with new (0) meter readings. As such, the meters in the
new assets will have a lower meter reading than the original equipment it
replaced.

I am thanking you, in advance, for any assistance.

--
Dan Johnson