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
from
(
    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
from
(
	select distinct Year(OrderDate) as YR, 
	       CustomerID 
	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!


Copyright© GeeksEngine.com