Identify and count the various entries in a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How to get Access (2003) to return every different entry in a field and how
often it occurs? i.e. The field "Qualifier" contains probably 5-10
different qualifiers (<T, =<W, null, etc.) in a million-record database. I
don't know in advance what all the different qualifiers are and would like
them listed with a count for each one.

Thanks
 
Create a new query in query design view and insert your field Qualifier twice
into the query grid. Click on the sigma (totals) button on the toolbar. Leave
the first column total as 'group by' but change your second column to 'count'
and then run the query.
 
SELECT Qualifier, Count(Qualifier) AS QualifierCount
FROM YourTable
GROUP BY Qualifier;
 
The following query will do it (substitute your table name for
"some_table"):

SELECT Qualifier, Count(Qualifier) FROM some_table GROUP BY Qualifier

If you are not familiar with SQL such as this:

Open the query designer
Choose "SQL view" from the View menu
Paste the above text into the blank window
Change the table name
Switch back to the query design view by choosing "Design View" from the View
menu
 
Back
Top