Custom Search

How to count distinct records in MS Access

I was updating an Access application the other day and needed to get a distinct count of some records in an Access table which is similar to the sample query below from Access Northwind database.

select count(distinct ReportsTo) as num_of_managers
from Employees

However, Access kept giving me the syntax error message shown below.

Syntax error (missing operator) in query expression 'count(distinct ReportsTo)'.

It turns out that Access Jet Engine does not support count(distinct xxx) syntax, which is supported by all major DBMS systems such as SQL Server, Oracle, MySQL, etc.

The solution to this problem is to write a sub-query in the FROM clause to retrieve all the qualified distinct records, then in the outer query in the SELECT statement, do a count for the number of records returned by the sub-query.

Count distinct records in Access

In the query below, we first get all the distinct records in the sub-query (inner query) in the FROM clause for ReportsTo column. This should give us a unique list of all the managers in Northwind company. We then, in the outer query, count the number records in the result returned from the sub-query. This achieves the same effect as using count(distinct ReportsTo).

When sub-query is used in the FROM clause, it can be viewed as selecting data from a virtual table because the result set of the sub-query is temporarily stored in memory and then used by the outer query as a table.

select count(ReportsTo) as num_of_managers
    select distinct ReportsTo from Employees

2 records returned.

Count distinct records that contain NULL values

The query below is the inner query we just saw which shows a list of distinct values in the ReportsTo column. Note that when we run this query alone, NULL is displayed in the result set, but this NULL value is not counted as a distinct value in the example query shown above when we count the number of distinct values.

select distinct ReportsTo 
from Employees

3 records returned, including NULL value.

Lesson learned here is that NULL values are not counted when doing distinct count in Access. This is also true for other DBMS systems when using count(distinct xxx) syntax.

Distinct count in each group of data

If we need to count the number of distinct records in a group of data such as in each year or in each product category, we need to retrieve all the groups without aggregation in the sub-query and then use GROUP BY on the groups in the outer query. See example below.

Here we want to count how many distinct customers that ordered products from Northwind each year.

First, because the Orders table does not have year column, we need to retrieve the year part from the OrderDate column. This is done by using Year function on OrderDate column in the sub-query and using YR as column alias. By using the distinct function, the sub-query gives us a unique list of CustomerIDs each year. Then the outer query aggregates the result set from the sub-query by using GROUP BY on the YR column and puts a count on the CustomerID column. So the end result is that we get a distinct count of CustomerIDs for each year.

select YR, 
       count(CustomerID) as Yearly_Distinct_Customer_Count
	select distinct Year(OrderDate) as YR, 
	from Orders
group by YR
order by YR

3 records returned.

Please note that, when I was working on the examples, I used the SQL View of MS Access. It's a bit awkward to use the Design View for creating sub-queries. If you're not familiar with writing queries in SQL View, practice makes perfect, especially if you are an Access VBA programmer. You'll find writing SQL in VBA is a must-have and very powerful way when working on complex business applications. The only exception I have found so far is that, when joining more than 2 tables in Access, we'd better use the Design View first because the JOIN syntax in Access is different to ANSI standards when joining more than two tables. In this case, I normally let the Design View create the join syntax, then I switch to SQL View and copy the SQL to VBA for further manipulation and development.

Happy Coding!


Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to do transaction based processing in MS Access
4.How to open a document (local/network file or web page) from MS Access
5.How to use ADOX to create unique composite index - the VBA approach
6.How to do cross-table update queries in MS Access - the right way
7.Three efficient ways to get the number of records by using VBA
8.How to create a composite unique index (not as a primary key) in MS Access
9.Use VBA to get the correct number of records in a Recordset object
10.Disable Access Prompt when a record is changed, table deleted, or action queries run
11.How to hide and unhide a MS Access object
12.How to return multiple values from a VBA function (Part 3)
13.How to return multiple values from a VBA function (Part 2)
14.How to return multiple values from a VBA function (Part 1)
15.Three ways to programmatically duplicate a table in MS Access by VBA
16.Create a DLL by CSharp or VB.Net for VBA
17.How to correctly reference and call a DLL
18.How to register a C# or VB.Net DLL
19.Email address validation by Regular Expressions using VBA
20.Fix MS Access error: Query must have at least one destination field
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

Home | Feedback | Terms of Use | Privacy Policy