CODEWITHSUNDEEP
ms-access
Asaf Nevo
Asaf Nevo

Reputation: 11678

Microsoft Access 2010 Max query on Count results

I need my access db i'm trying to use the Max function on the results of a Count function of a field i couldn't find any way of doing it - not through the user interface and not through SQL query.

This is my screen: enter image description here

in the screen i have the count function working properly

how can i run the Max function on the Count function results?

Upvotes: 1

Views: 7392

Answers (2)

RAPTOR
RAPTOR

Reputation: 124

I would use the TOP 1 SQL and DESC function. Or in the designer view it's "Return".

For example:

SELECT TOP 1 the_column_to_display
FROM the_table
ORDER BY Count(the_column_to_count) DESC;

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123654

To "run the Max function on the Count function results" requires that you "roll up" your count results to a higher level of aggregation. Save your existing query as HallCounts and then create a new query that does something like

SELECT Country_Id, Max(CountOfHall_Id) AS MaxHallCount 
FROM HallCounts 
GROUP BY Country_Id;

Or, to select just the row(s) with the highest count, try something like this

SELECT * FROM HallCounts 
WHERE CountOfHall_ID = (SELECT MAX(CountOfHall_ID) FROM HallCounts);

Upvotes: 2

Related Questions