Search code examples
sqlms-accessgroup-bymax

MS ACCESS SQL query needing multi-level grouping and Top 1 COUNT results

I am creating a report. In the report, I am to summarize who is the night operator for the week. I receive the incorrect information when a substitute works the first day. I am attempting to count the number of days an operator works during the (SORTWEEK) at each (SITE). I'm struggling with how to select the correct operator for each site during each week. This is as close as I have come, but cannot get it to only show me the top record from each SITE each SORTWEEK. I know it has to do with the order of operations, but cannot figure out what I need to change.

SELECT [All].SORTWEEK, [All].SITE, [All].NOPER, COUNT([All].NOPER)
FROM [All]
GROUP BY [All].SORTWEEK, [All].SITE, [All].NOPER
ORDER BY [All].SORTWEEK, [All].SITE, COUNT(NOPER) DESC;
SORTWEEK SITE NOPER Cnt
1/18/2023 18B TODD 5
1/18/2023 18B ZACH 2
1/18/2023 1A TODD 5
1/18/2023 1A ZACH 2
1/18/2023 1B TODD 5
1/18/2023 1B ZACH 2
1/18/2023 2A2B TODD 5
1/18/2023 2A2B ZACH 2
1/18/2023 3AB TODD 5
1/18/2023 3AB ZACH 2
1/18/2023 3D TODD 5
1/18/2023 3D ZACH 2
1/18/2023 4AB TODD 5
1/18/2023 4AB ZACH 2

The list goes on, and most often there are only 2 names, but I would like the name of the person who put in the most days to show on the report.

Here's another failed attempt, but I don't understand the errors MSACCESS is throwing.

SELECT [All].SITE, [All].SORTWEEK, (SELECT TOP 1 NOPER FROM 
(SELECT [All].NOPER, COUNT([All].[NOPER])
    FROM [All] AS [Temp] 
    WHERE [Temp].[SITE] = [All].[SITE] 
    AND [All].[SORTWEEK] = [Temp].[SORTWEEK]
    ORDER BY COUNT([All].NOPER) DESC)) AS TOPNOP
FROM [All]
GROUP BY SORTWEEK, SITE, TOPNOP
ORDER BY [All].SITE, [All].SORTWEEK, [All].NOPER;

I can tell I am getting closer. The following query gives me the correct result after it prompts me for the SITE and SORTWEEK, but I can't get it to show all results.

SELECT [All].SITE, [All].SORTWEEK, 
    (SELECT TOP 1 NOPER FROM 
        (SELECT [All].NOPER, COUNT(NOPER)
        FROM [All]
        WHERE [Temp].SORTWEEK = [All].SORTWEEK
        AND [Temp].SITE = [All].SITE
        GROUP BY [All].SORTWEEK, [All].SITE, [All].NOPER
        ORDER BY COUNT([All].NOPER) DESC) 
    ) AS [#Temp]
FROM [All]
WHERE [Temp].SORTWEEK = [All].SORTWEEK
AND [Temp].SITE = [All].SITE
GROUP BY [All].SORTWEEK, [All].SITE
ORDER BY [All].SITE, [All].SORTWEEK;
Solution

  • Using the posted dataset (a saved aggregate query object) named Temp as starting point, consider:

    SELECT * FROM Temp WHERE NoPer IN (SELECT Top 1 NoPer FROM Temp AS Dupe
WHERE Dupe.Sortweek = Temp.Sortweek AND Dupe.Site = Temp.Site ORDER BY Cnt DESC);

    TODD is the top Cnt for each date/site pair.