Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

ms-access

People also ask

Can you do distinct count in access?

Some database systems support aggregate functions which can directly count distinct values in one query. Access does not support such a function, so you must first write a query which gets distinct values for the Site column. A query can always refer to another query in the same manner it refers to a table.

How do you count unique records?

SELECT COUNT (DISTINCT item_num) FROM items; If the COUNT DISTINCT function encounters NULL values, it ignores them unless every value in the specified column is NULL. If every column value is NULL, the COUNT DISTINCT function returns zero (0).

How do I count the number of unique values in a column in a table?

You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM(IF(1/COUNTIF(data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears.

How do I not count duplicates in access?

You can prevent duplicate values in a field in an Access table by creating a unique index. A unique index is an index that requires that each value of the indexed field is unique.


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.