Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    56

    Unanswered: Querying an Equipment Hours Log

    Access 2010

    This is the table layout:

    HoursLogID
    ComponentID
    HoursLogDate
    HoursReading
    Serviced


    Basically the table is used to record a component ID (unique to each engine), the date of the hours reading (usually once a week), the hour meter reading at that date, and a yes/no field for serviced (indicates if the engine was serviced via our predefined schedule).

    My goal has been to select the most recent (to the date the queries are ran) HoursReading for each unique ComponentID. I know that I have to use DateDiff() between HoursLogDate and Date(). Then use the Min() function on that DateDiff() value. Problem comes when I want to do the Min() per each unique ComponentID. I am under the impression it may take a couple of queries to get it to work. but I am confused on the order of doing each function.

    Is it possible? Any input is greatly appreciated.

  2. #2
    Join Date
    Aug 2011
    Posts
    56
    Figured out the SQL to get the queries I wanted, but developed another roadblock.

    Here was my solution to getting the most recent hours and most recent service per ComponentID from the HoursLog:

    Query 1 - LogDateMinDiff
    SELECT HoursLog.ComponentID, Min(DateDiff("y",[HoursLogDate],Date())) AS DateMinDiff, HoursLog.Serviced
    FROM HoursLog
    GROUP BY HoursLog.ComponentID, HoursLog.Serviced;

    Query 2 - LogRecentHours
    SELECT HoursLog.HoursLogID, HoursLog.ComponentID, HoursLog.HoursLogDate, HoursLog.HoursReading, DateDiff("y",[HoursLog]![HoursLogDate],Date()) AS DateDiff, HoursLog.Serviced
    FROM HoursLog, LogDateMinDiff
    WHERE (((DateDiff("y",[HoursLog]![HoursLogDate],Date()))=[LogDateMinDiff].[DateMinDiff]) AND ((HoursLog.Serviced)=False) AND (([HoursLog]![ComponentID])=[LogDateMinDiff]![ComponentID]));

    Query 3 - LogRecentService
    SELECT HoursLog.HoursLogID, HoursLog.ComponentID, HoursLog.HoursLogDate, HoursLog.HoursReading, DateDiff("y",[HoursLog]![HoursLogDate],Date()) AS DateDiff, HoursLog.Serviced
    FROM HoursLog, LogDateMinDiff
    WHERE (((DateDiff("y",[HoursLog]![HoursLogDate],Date()))=[LogDateMinDiff].[DateMinDiff]) AND ((HoursLog.Serviced)=True) AND (([HoursLog]![ComponentID])=[LogDateMinDiff]![ComponentID]));


    So far its working. Now I need to compare the HoursReadings between Query 2 & 3.....

  3. #3
    Join Date
    Aug 2011
    Posts
    56
    To get current hours from last service:

    Query 4 - ServiceHoursCurrent
    SELECT Equipment.EquipmentNumber, EquipmentType.EquipmentType, Component.ComponentID, Component.ComponentLookup, LogRecentHours.HoursLogDate AS LogRecentHours_HoursLogDate, LogRecentHours.HoursReading AS LogRecentHours_HoursReading, LogRecentService.HoursLogDate AS LogRecentService_HoursLogDate, LogRecentService.HoursReading AS LogRecentService_HoursReading, DateDiff("y",[LogRecentService_HoursLogDate],[LogRecentHours_HoursLogDate]) AS LastServiceDate, [LogRecentHours_HoursReading]-[LogRecentService_HoursReading] AS LastServiceHours
    FROM (EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentTypeID) INNER JOIN ((Component INNER JOIN LogRecentService ON Component.[ComponentID] = LogRecentService.[ComponentID]) INNER JOIN LogRecentHours ON Component.[ComponentID] = LogRecentHours.[ComponentID]) ON Equipment.EquipmentID = Component.EquipmentID;


    To set warning limits:
    Query 5 - ServiceHoursWarning
    SELECT ServiceHoursCurrent.EquipmentNumber, ServiceHoursCurrent.EquipmentType, ServiceHoursCurrent.ComponentID, ServiceHoursCurrent.ComponentLookup, ServiceHoursCurrent.LastServiceDate, ServiceHoursCurrent.LastServiceHours
    FROM ServiceHoursCurrent
    WHERE (((ServiceHoursCurrent.LastServiceHours)>=200));


    I figured I would post my solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •