query for most often occurrence

[Linda]

I am attempting to query an access database for the
account number (text field) that occurs most often. How
do I do this?

 

[John Spencer (MVP)]

UNTESTED and might not work, but try

SELECT TOP 1 AccountNumber
FROM TableName
GROUP BY AccountNumber
ORDER BY Count(AccountNumber) Desc

 

[Andrew Smith]

SELECT TOP 1 AccountNumber
FROM tblName
GROUP BY AccountNumber
ORDER BY Count(AccountNumber) DESC;

(Replace tblName and AccountNumber with your table/field names)

 

[Howard Brody]

Let's assume we're looking at the tblSales table and
trying to find the Account that appears most often.

Open a new query in Design view and select the tblSales table.
Drag the [Account] field down three times (yes, three times)
Click on View > Totals to display the Totals line for each field.
For the first [Account] field, select "First" in the Totals line.
For the second [Account] field, select "Group By" in the Totals line
For the third [Account] field, select "Count" in the Totals line
Also for the third [Account] field, SortDescending.

This will display each account and the number of times it
appears in the tblSales table.

Hope this helps!

Howard Brody