How Do I Count Unique Items in Field in Access Query?
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...
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?
3 Answers
Try this
SELECT Count(*) AS N
FROM
(SELECT DISTINCT Name FROM table1) AS T;
Read this for more info.
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.
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.
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
