Counting the greatest number

  • Thread starter Thread starter peljo via AccessMonster.com
  • Start date Start date
P

peljo via AccessMonster.com

I want to build a query with which to find the greatest number of figures in
the field.I have a field called Plates where single numbers are entered.For
example

Visits Plates
John 3
Jim 3
Deuce 3
Liza 3
Lenny 2
Lorry 6

Obvisouly in the above example we can count that the number 3 is the biggest
number.
How can i express that in a query ?
 
SELECT Your_Table.Plates, Count(Your_Table.Plates) AS CountOfPlates
FROM Your_Table
GROUP BY Your_Table.Plates;
 
I would try the following.

SELECT Distinct TOP 1 Plates
FROM YourTable
GROUP BY Plates
ORDER BY Count(Plates) DESC

If you want to return the associated records
SELECT *
FROM YourTable
WHERE Plates in
(SELECT Distinct TOP 1 Plates
FROM YourTable
GROUP BY Plates
ORDER BY Count(Plates) DESC)

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