query for most often occurrence

  • Thread starter Thread starter Linda
  • Start date Start date
L

Linda

I am attempting to query an access database for the
account number (text field) that occurs most often. How
do I do this?
 
UNTESTED and might not work, but try

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

(Replace tblName and AccountNumber with your table/field names)
 
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.
Click to expand...

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

Hope this helps!

Howard Brody
 
Back
Top