含聚合函数的MS Access/SQL子查询语法及设备维保查询需求咨询

To get the next scheduled service date for each device, we need to account for two scenarios: devices that have been serviced before (use the latest service date plus the maintenance period) and devices that have never been serviced (use the purchase date plus the maintenance period). Here's how to implement this with a subquery and aggregate functions in MS Access SQL:

Final Query #

SELECT 
    i.[DeviceID],
    i.[Device Name], -- Replace with actual Inventory fields you want to display
    i.[Purchase Date],
    i.[Service Period],
    IIF(ls.LastServiceDate IS NOT NULL, 
        DateAdd('m', i.[Service Period], ls.LastServiceDate),
        DateAdd('m', i.[Service Period], i.[Purchase Date])
    ) AS NextServiceDate
FROM Inventory AS i
LEFT JOIN (
    -- Subquery to get the most recent service date for each device
    SELECT [DeviceID], Max([Work Date]) AS LastServiceDate
    FROM WorkDone
    GROUP BY [DeviceID]
) AS ls ON i.[DeviceID] = ls.[DeviceID];

Breakdown of the Query #

1. Subquery for Latest Service Date:

   • The inner subquery groups the WorkDone table by DeviceID and uses Max([Work Date]) to fetch the most recent service date for each device. This gives us the last time each device was serviced.

2. LEFT JOIN with Inventory:

   • We use a LEFT JOIN to combine the Inventory table with the subquery results. This ensures that even devices with no service records (no entries in WorkDone) are included in the output.

3. Calculate Next Service Date:

   • The IIF function checks if a device has a recorded last service date:
     • If yes: Add the Service Period (in months) to the latest service date using DateAdd('m', ...).
     • If no: Use the Purchase Date as the starting point, adding the Service Period to get the first scheduled service date.

Notes #

• Ensure both tables share a common identifier (like DeviceID) to correctly link devices between Inventory and WorkDone.
• If you only want to include devices that have been serviced at least once, replace LEFT JOIN with INNER JOIN.
• Your initial approach of using Max(DateAdd(...)) would also work for devices with service records, but it’s less efficient than first fetching the latest service date and then calculating the next date. The subquery method is cleaner and performs better, especially with large datasets.

内容的提问来源于stack exchange，提问作者J.Warren