If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need to create a progress meter while an Access module runs | Brent E | General Discussion | 5 | January 28th, 2005 11:25 PM |
Lowest Cost at the latest Change Date | Luis | General Discussion | 1 | December 18th, 2004 02:50 AM |
query progress meter | Bob White | General Discussion | 8 | December 6th, 2004 11:57 AM |
Trying to get difference in Meter readings between rows | Mike S. | Running & Setting Up Queries | 2 | June 8th, 2004 07:05 PM |
Outlook 2003 Reading Pane (preview pane) | Kristy | General Discussion | 3 | June 3rd, 2004 11:26 PM |