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

User · Accepted Answer

Try this  SELECT Count    AS N FROM  SELECT DISTINCT Name FROM table1  AS T    Read this for more info

User · Answer

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

User · Answer

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    gt 0        Hope this helps  not the best way I am sure  and Access should have had this built in

[sql] How do I count unique items in field in Access query?

Examples related to sql

Examples related to ms-access