Syntax of MS Access/SQL sub-query including aggregate functions

Question

I am trying to produce a database to manage maintenance of equipment. I have two tables:

One (Inventory) containing details of each piece of equipment, including Purchase Date and Service Period,
One containing details of work done (WorkDone), including the date the work was carried out (Work Date).

I would like a query that displays the date that it should be next serviced. So far I have:

SELECT Max(DateAdd('m', [Inventory].[Service Period], 
                        [WorkDone].[Work Date])) AS NextServiceDate, 
       Inventory.Equipement
FROM Inventory INNER JOIN WorkDone ON Inventory.ID = WorkDone.Equipment
GROUP BY Inventory.Equipement

This works well as long as some work done has been registered for a given piece of equipment. If no work has been carried out I would like the NextServiceDate to also show

DateAdd('m',[Inventory].[Service Period], [Inventory].[Purchase Date])

However, I cannot work out how to get SQL/MS access to compare two values and only display the greater of the two. From reading around I think I should be able to do a sub-query, but I cannot work out how to phase it.

I've been trying to adapt @MikeTeeVee's answer from here: Is there a Max function in SQL Server that takes two values like Math.Max in .NET?. But I keep getting errors saying that query is not part of an aggregate function and I'm not certain what I doing wrong. For example, I tried:

SELECT Inventory.Equipement,
       (SELECT MAX(NSD_proxy) 
        FROM (VALUES 
             (Max(DateAdd('m', Inventory.[Service Period], WorkDone.[Work Date]))), 
                 (DateAdd('m', Inventory.[Service Period], Inventory.[Purchase Date]))) 
         AS FUNCTION(NSD_proxy)
        ) AS NextServiceDate,
FROM Inventory INNER JOIN WorkDone ON Inventory.ID = WorkDone.Equipment
GROUP BY Inventory.Equipement

which has some syntax error.

SunKnight0 · Answer 1 · 2018-03-30T15:42:06.353

1

You don't have to compare the two dates, just check if a WorkDone record exists to match the Inventory record.

You can use:

IIF(ISNULL(WorkDone.Equipment),
DateAdd('m',[Inventory].[Service Period],[Inventory].[Purchase Date]),
Max(DateAdd('m',[Inventory].[Service Period],[WorkDone].[Work Date])))
AS NextServiceDate

The rest of your query can remain as is.

edited Mar 30 '18 at 15:42

answered Mar 30 '18 at 15:31

SunKnight0

3,331
1
10
8

This gives me an error saying: "Your query does not include the specified expression 'IIF(ISNULL(WorkDone.Equipment), DateAdd('m',[Inventory].[Service Period],[Inventory].[Purchase Date]), Max(DateAdd('m',[Inventory].[Service Period],[WorkDone].[Work Date])))' as part of an aggregate function." – J.Warren Mar 31 '18 at 05:39
Just use `MAX(IIF(...)) AS.NextServiceDate` – SunKnight0 Apr 03 '18 at 17:45

score 1 · Accepted Answer · answered Mar 30 '18 at 16:35

1

Consider a LEFT JOIN to return matched or unmatched records where latter is filled with NULLs, and then run your aggregate, MAX, with an NZ():

SELECT Max(NZ(DateAdd('m', i.[Service Period], w.[Work Date]),
              DateAdd('m', i.[Service Period], i.[Purchase Date]))
          ) AS NextServiceDate, i.Equipement
FROM Inventory i LEFT JOIN WorkDone w ON i.ID = w.Equipment
GROUP BY i.Equipement

answered Mar 30 '18 at 16:35

Parfait

104,375
17
94
125

Edit: Actually I have a further question as I would not like to apply a criteria: I would like to return all entries where NextServiceDate is earlier than a weeks time. I have tried: `HAVING Max(NZ(DateAdd('m', i.[Service Period], w.[Work Date]), DateAdd('m', i.[Service Period], i.[Purchase Date]))) < DateAdd('ww',1,Date())` but this only compares the Day part of the date. So for example today is the 31/3/18 so it's showing all entries that have a NextServiceDate in the first 6 days of any month. (I have a UK system so the date format is dd/mm/yyyy just in case that effects things). – J.Warren Mar 31 '18 at 06:44

Syntax of MS Access/SQL sub-query including aggregate functions

2 Answers2

Linked