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

Admin 1
Viewed 234398

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?

ms-access sql
3 Answers

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.

user_113695270
Related