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 · 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]))>0))
);

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

User · 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

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

Examples related to sql

Examples related to ms-access