Results 1 to 3 of 3
  1. #1
    Kirtap is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    22

    Trying to get the date of latest maintenance performed on a piece of equipment

    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.
    Last edited by Kirtap; 02-12-2014 at 02:28 PM. Reason: Changed query

  2. #2
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Since your using Max(Maintenance_history.Date) you don't need the maintenance_history.date in the group by

    Code:
    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;

  3. #3
    Kirtap is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    22
    Hi. Sorry for the late reply. Thank you very much! I appreciate it!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Value of latest date
    By v!ctor in forum Queries
    Replies: 3
    Last Post: 02-27-2013, 03:48 PM
  2. Populate table after DLookup is performed
    By OMGsh Y did I say Yes in forum Forms
    Replies: 16
    Last Post: 03-21-2011, 11:25 AM
  3. Selecting Earliest and latest date
    By kstyles in forum Queries
    Replies: 10
    Last Post: 12-31-2010, 03:04 PM
  4. How do you get the latest date?
    By radicrains in forum Queries
    Replies: 6
    Last Post: 10-27-2010, 08:59 PM
  5. Latest Date Records
    By Rick West in forum Queries
    Replies: 1
    Last Post: 09-25-2009, 11:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums