How do I count unique items in field in Access query?

Question

My Table: table1

ID  Name  Family
1   A     AA
2   B     BB
3   A     AB
4   D     DD
5   E     EE
6   A     AC

SQL command on Access:

select count(*) from table1

Output: ------------> True
6 row(s)

I tried to count unique names:

Expected output: 4 row(s)

select count(distinct Name) from table1

Output on Access: ------------> Error

What changes do I need to make to my query?

Bullnose

Answer 1

Try this

SELECT Count(*) AS N
FROM
(SELECT DISTINCT Name FROM table1) AS T;

Read this for more info.

Groh

I think even this SELECT count(*) FROM (SELECT DISTINCT Name FROM table1); will do. – Unkind

Yeah may be, I'm not sure but having an alias is always better so that alias can be used for fetching other columns but in this particular case you are right if "Access supports that syntax" of course. – Groh

We're sorry, but we can't find the page you're looking for. Link is dead – Photoactive

@SubirKumarSao This worked for me. Simple and sweet. – Huba

Answer 2

Access-Engine does not support

SELECT count(DISTINCT....) FROM ...

You have to do it like this:

SELECT count(*) 
FROM
(SELECT DISTINCT Name FROM table1)

Its a little workaround... you're counting a DISTINCT selection.

Lenrow

Answer 3

A quick trick to use for me is using the find duplicates query SQL and changing 1 to 0 in Having expression. Like this:

SELECT COUNT([UniqueField]) AS DistinctCNT FROM
(
  SELECT First([FieldName]) AS [UniqueField]
  FROM TableName
  GROUP BY [FieldName]
  HAVING (((Count([FieldName]))>0))
);

Hope this helps, not the best way I am sure, and Access should have had this built in.

Amick