count the number of unique records in a field within Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RM - 20 Nov 2007 05:06 GMT

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

Reply to this Message

Ken Snell (MVP) - 20 Nov 2007 05:57 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

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

Reply to this Message

Allen Browne - 20 Nov 2007 09:37 GMT

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.)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

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

Reply to this Message

John Spencer - 20 Nov 2007 12:27 GMT

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

Signature

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

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

Reply to this Message