count the number of unique records in a field within Access

[Guest]

How do I count the number of unique records in a field within Access?

 

[Ken Snell \(MVP\)]

SELECT NameOfField, Count(*) AS HowMany
FROM TableName
GROUP BY NameOfField;

 

[Allen Browne]

Here's an ECount() function that extends the functionality of the DCount()
in Access:
http://allenbrowne.com/ser-66.html

It includes an optional argument to ask for distinct count (i.e. number of
unique values in the specified field.)

 

[John Spencer]

Access does not support the DISTINCT predicate in the aggregate functions so
you have to build a Distinct query and then count

SELECT Count(*)
FROM
(SELECT Distinct FieldOne
FROM TableA) as UniqueFieldOne


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