Re: Latest meter reading

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <dartuso@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:uGiqIPdSFHA.3176@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <dan_johnson@xxxxxxxxxxx> wrote in message
> news:%23kKe7$bSFHA.3296@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>


.

Relevant Pages