Hello,
I am trying to get the date for the latest maintenance performed on each piece of equipment.
Two tables I am focusing on for now are called Assets and Maintenance_History. Maintenance_History has a field that links each record to the Asset it belongs to.
So its like:
Assets-
Asset_ID
*other fields*
Maintenance_History
Maintenance_ID,
Maintenance_Asset_Link,
Date,
*other fields*
Asset ID and Maintenance_ID are primary keys.
So here is a query I wrote with the intention of getting the date of the latest calibration performed on an asset.
SELECT Assets.Asset_ID, Max(Maintenance_History.Date)
FROM Assets Inner JOIN Maintenance_History ON Assets.Asset_ID = Maintenance_History.Maintenance_Asset_Link
GROUP BY Assets.Asset_ID, Maintenance_History.Date;
I was hoping that Max(Maintenance_History.Date) would just grab the latest maintenance date for an asset.
What am I not understanding about how Max and Group By works?
Thank you in advance.