Calculating Distinct Counts in Access

by Patrick G. Matthews

1. Introduction

All major SQL dialects allow for a Count aggregate function, and Access's native Jet SQL is no exception.  As in other database platforms, Access's Count() (as well as the "domain aggregate" DCount()) function returns the number of records pertaining to a grouped result set and meeting the criteria specified in the query (in the case of Count()) or in DCount() in the third argument of the function.

Consider these example data:

Category  Num
                      -------------
                      x         1
                      x         1
                      x         2
                      x         3
                      y         1
                      y         1
                      z         1
                      z         1
                      z         2

Select all Open in new window

In this case, it is very simple to get a count of items for each Category:

SELECT Category, Count(*) AS Items
                      FROM SomeTable
                      GROUP BY Category
                      
                      returns:
                      Category  Num
                      -------------
                      x         4
                      y         2
                      z         3

Select all Open in new window

Suppose, however, that instead of getting the count of each item for a given category, you instead wanted to know the count of distinct items for each category.  SQL Server's Transact SQL and some other SQL dialects support the Count(Distinct ColumnName) syntax:

SELECT Category, Count(Distinct Num) AS DistinctItems
                      FROM SomeTable
                      GROUP BY Category
                      
                      returns:
                      Category  Num
                      -------------
                      x         3
                      y         1
                      z         2

Select all Open in new window

However, Access's Jet SQL does not support this syntax.  This article will demonstrate three techniques for computing distinct counts.  The first approach uses two queries for each distinct count to be performed.  The second uses sub-queries to accomplish the same thing in one SQL statement.  Lastly, the third uses Visual Basic for Applications (VBA) code to create a user-defined function, thus simplifying the operation further but at the cost of some execution speed.

2. Distinct Counts Using 'Two-Step' Queries

One way to generate distinct counts in Access is to utilize what I refer to as a "two-step" process:

Using the simple example from Section 1, to determine the count of distinct values in the Category column using the two-step process, we would first create a "Step1" query such as:

SELECT Category
                      FROM SomeTable
                      GROUP BY Category

Select all Open in new window

If we save that as "Step1", we would then get out final result by creating a query that uses Step1 as its source:

SELECT Count(*) AS DistinctCategories
                      FROM Step1

Select all Open in new window

Using the same example, if we wanted to know the count of distinct Num values for each Category, we could use this SQL for Step1:

SELECT Category, Num
                      FROM SomeTable
                      GROUP BY Category, Num

Select all Open in new window

The SQL for Step2 then becomes:

SELECT Category, Count(Num) AS DistinctNum
                      FROM Step1
                      GROUP BY Category

Select all Open in new window

In the attached sample file (see Section 5), you will see four sample queries that demonstrate this technique:

3. Distinct Counts Using Sub-queries

In addition to the "two-step" process described above, you can generate distinct counts from a single SQL statement by using sub-queries.  The sub-query defines a derived table that has only distinct rows in it, and then the main query performs the aggregation on that derived table.  That derived table should include in its SELECT clause all of the columns for which you want a distinct count, as well as all of the columns that you wish to group on.

Indeed, that is exactly what Access is doing implicitly in the "two-step" approach outlined in Section 2: when Access runs the "Step2" query, it is in effect treating the corresponding "Step1" query as a derived table.

Using the simple example from Section 1, suppose we needed to determine the count of distinct values in the Category column.  To determine that using a sub-query:

SELECT Count(z.Category) AS DistinctItems
                      FROM
                          (SELECT Category
                          FROM SomeTable
                          GROUP BY Category) AS z

Select all Open in new window

Using the same example, if we wanted to know the count of distinct Num values for each Category, we could use:

SELECT z.Category, Count(z.Num)
                      FROM
                          (SELECT s.Category, s.Num
                          FROM SomeTable s
                          GROUP BY s.Category, s.Num) AS z
                      GROUP BY z.Category

Select all Open in new window

Please note that if you enter SQL similar to the above into Access's query designer, after saving the query Access will adjust the SQL statement so that they will look more like this:

SELECT Count(z.Category) AS DistinctItems
                      FROM [SELECT Category FROM SomeTable GROUP BY Category]. AS z
                      
                      SELECT z.Category, Count(z.Num)
                      FROM [SELECT s.Category, s.Num FROM SomeTable s GROUP BY s.Category, s.Num]. AS z
                      GROUP BY z.Category

Select all Open in new window

Note: In the SQL examples using the sub-query approach, you may have noticed that I used an alias ("z") for the derived table returned by the sub-query.  While aliases are usually optional and are thus used mainly to improve your SQL's readability, in this case Jet SQL actually does require the alias.  Jet will auto-create an alias if needed, but it can only handle one; therefore, it is always a best practice to declare your own alias when using sub-queries so you have full knowledge and control.

In the attached sample file (see Section 5), you will see four sample queries that demonstrate this technique:

4. DCountDistinct Function

While you can generate distinct counts using "two-step" queries (Section 2) or sub-queries (Section 3), you can also accomplish this by using VBA to create a user-defined function.  Thus, I have created the DCountDistinct() function.  This function provides similar functionality to the native DCount() domain aggregate function, but instead of counting all rows from the specified result set, DCountDistinct returns the number of distinct entries meeting the specified criteria.

The source code for DCountDistinct is as follows:

Function DCountDistinct(CountCols As String, Tbl As String, Optional Criteria As String = "")
                          
                          ' Function by Patrick G. Matthews
                          
                          ' Requires reference to Microsoft DAO library
                          
                          ' This function provides "domain aggregate"-type functionality similar to
                          ' COUNT(DISTINCT ColumnName) in Transact-SQL (i.e., SQL Server's dialect).  The function
                          ' can be used in queries, forms, and reports in the Access UI and/or in VBA code once added
                          ' to a regular VBA module
                          
                          ' CountColumns is a comma-delimited list of columns for which we are counting the distinct
                          '   values (or combinations of values if 2+ columns are specified).  Place field names in
                          '   square brackets if they do not meet the customary rules for naming DB objects
                          ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
                          '   if they do not meet the customary rules for naming DB objects
                          ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
                          '   or Or as needed to build the right logic, and to encase text values in single quotes
                          '   and dates in #
                          
                          ' Just like DCount, if there are no items found matching the criteria, DCountDistinct
                          ' returns zero
                          
                          Dim rs As DAO.Recordset
                          Dim SQL As String
                          
                          ' Enable error handler
                          
                          On Error GoTo ErrHandler
                          
                          ' Build query that counts the number of distinct items (or combinations of items, if
                          ' CountCols specifies 2+ columns) that meet the specified criteria.  If no criteria are
                          ' specified, then count goes against entire table
                          
                          SQL = "SELECT Count(1) AS Result " & _
                              "FROM (" & _
                                  "SELECT DISTINCT " & CountCols & " " & _
                                  "FROM " & Tbl & " " & _
                                  IIf(Criteria <> "", "WHERE " & Criteria, "") & _
                              ")"
                              
                          ' Open recordset with result
                          
                          Set rs = CurrentDb.OpenRecordset(SQL)
                          
                          ' Set return value
                          
                          DCountDistinct = rs!Result
                          
                          rs.Close
                          
                          GoTo Cleanup
                          
                      ErrHandler:
                          DCountDistinct = CVErr(Err.Number)
                          
                      Cleanup:
                          Set rs = Nothing
                          
                      End Function

Select all Open in new window

DCountDistinct takes the following arguments:

To use this function in your project, simply add the code to a regular VBA module, and make sure to set a reference to the latest version of Microsoft Data Access Objects (DAO) installed on your computer.  Please note that using DCountDistinct() will probably be slower than using the "two-step" or sub-query approaches explored in Sections 2 and 3, but in small to medium data sets you are unlikely to notice the difference, and the convenience the function offers may outweigh the increased runtime.

Using the simple example from Section 1, we can determine the count of distinct values in the Category column using DCountDistinct():

SELECT DCountDistinct("Category", "SomeTable") AS DistinctItems

Select all Open in new window

Using the same example, if we wanted to know the count of distinct Num values for each Category, we could use:

SELECT Category, DCountDistinct("Num", "SomeTable", "[Category] = '" & Category & "'")
                      FROM SomeTable
                      GROUP BY Category

Select all Open in new window

In the attached sample file (see Section 5), you will see four sample queries that demonstrate this technique:

5. Sample File

DCountDistinct.mdb

The attached sample file is a Microsoft Access 2003-format database containing a single table, "Sample", along with several queries demonstrating the count distinct approaches described above.  The file's VBA Project also contains the source code for the DCountDistinct function.


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=