Finding the maximum number from a count function

[saalford]

I'm using the query design view to take a list of buyers and usin
total, to count the number of times they appear...so far so good. Th
query returns something like
buyerID1(1)
buyerID2(3)
buyerID3(1)
buyerID4(3)
buyerID5(2),ect. What I need now is to set the criteria to only retur
the buyers who have the max count, buyers 2&4. I looked at the SQL vie
and tried adding Max infront of the Count, but I get an error abou
aggregate functions in the expression.

This gives me the counts
SELECT [Sale Lots].[Buyer ID], Count([Sale Lots].[Buyer ID]) A
[CountOfBuyer ID]
FROM [Sale Lots]
GROUP BY [Sale Lots].[Buyer ID];

Get the error when I try
SELECT [Sale Lots].[Buyer ID], Max(Count([Sale Lots].[Buyer ID])) A
[CountOfBuyer ID]

Is this possible in Design view or do I need some kind of where claus
in SQL?

Thank in advance for any help.
Scot

 

[John Spencer]

One method would be to use a TOP query.

SELECT Top 1 [Sale Lots].[Buyer ID]
, Count([Sale Lots].[Buyer ID]) AS[CountOfBuyer ID]
FROM [Sale Lots]
GROUP BY [Sale Lots].[Buyer ID]
ORDER BY Count([Sale Lots].[Buyer ID]) DESC

Since this returns all records tied for first place, it should give you
the specified result.

John Spencer
Access MVP 2001-2005, 2007

 

[saalford]

Thanks John! Worked like a charm.

Looks like changing from Descending to Ascending gives me the MI
(Bottom)results...excellent.

As a more generalized question, suppose I needed records whose coun
matched the average (or within Avg +/- 1). Or I wanted to do somethin
around summing the counts themselves. These are just hypotheticals, bu
I'm curious. I would think there is a way to use an aggregate functio
nested within another aggregate. Maybe some type of subquery. I'v
been playing around with the SQL statement but I can't get the synta
correct. Any other ideas?

If not, this gets what I needed.
Many thanks again for your quick and great advice.
Scot

 

[John Spencer]

Then you would need a subquery as you mentioned.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..