Need maximum value from count query for group.
Windows 8 
Access 2010 32bit
Ok .. I don't know if I can explain it, so it may just be easier to display what I am talking about and my SQL.
I have a query that gives me counts for each group ... fine, however I really just want the TOP 1 count for each group. I know .. there can be duplicate counts .. and in that case I would be ok with showing all of the values that are acceptable.
This is my SQL for the below ... but I would like to limit it to only the TOP 1 count.
For example below .. I only want to see the ETS Test records with count of 33 transmissions and ETS LRT Training with count of 450, so really only 1 record per group, unless there is a tie .. then I want to see all the tied records.
SELECT Kevin.User, Transmissions.Type, Transmissions.Group, KevinGroup.Name, Transmissions.Radio, Count(*) AS CountOfGroup1
FROM Kevin RIGHT JOIN (Transmissions LEFT JOIN KevinGroup ON Transmissions.Group = KevinGroup.Group) ON Kevin.Radio = Transmissions.Radio
GROUP BY Kevin.User, Transmissions.Type, Transmissions.Group, KevinGroup.Name, Transmissions.Radio
ORDER BY Count(*) DESC;
User Type Group Name Radio CountOfGroup1 Harris Tech Radio * Tr 65 ETS Test 16013 6 ETS Radio Tech * Tr 65 ETS Test 15813 1 Harris Tech Radio * Tr 65 ETS Test 16014 33 ETS Console * Tr 65 ETS Test 15019 13 ETS Console * Tr 65 ETS Test 15017 9 ETS Bus * Tr 65 ETS Test 13934 3 ETS Bus * Tr 65 ETS Test 14064 1 ETS Bus * Tr 65 ETS Test 13652 1 ETS Signals * Tr 67 ETS Lrt Training 15974 450 ETS Lrt Signals * Tr 67 ETS Lrt Training 15923 5 ETS Bus * Tr 67 ETS Lrt Training 15925 17 ETS Lrt Signals * Tr 67 ETS Lrt Training 15973 284 ETS LRT Signals * Tr 67 ETS Lrt Training 15684 198 ETS Bus Control * Tr 67 ETS Lrt Training 15004 2 ETS Signals * Tr 67 ETS Lrt Training 15917 1 ETS Lrt Maintenance * Tr 67 ETS Lrt Training 15930 27 ETS LRT Train * Tr 67 ETS Lrt Training 15901 8
Reply With Quote
