Count Distinct In Access: Part 4

Crosstab Method (Reader Submitted)

Note: In the comments section of Part 3: User-Defined Function, Patrick mentioned another method that uses a Crosstab Query.  Even though I didn't develop it, I'm including it in this series for completeness.  So, thank you, Patrick. 

SQL Server has a nice built-in function called COUNT DISTINCT, which is missing in Access SQL.

What does COUNT DISTINCT do?  Well, there are times when you want to count distinct values in a query, that is, a count of values without duplicates.  For instance, given the following table, how many distinct customers have orders?

ORDERS

OrderID	OrderDate	Customer	Amount
1	1/17/2008	Ajax Inc.	$310.00
2	1/17/2008	Ajax Inc.	$510.50
3	1/17/2008	Ajax Inc.	$311.00
4	1/17/2008	Baker Corp.	$5,144.00
5	1/17/2008	Baker Corp.	$61.00
6	1/17/2008	Baker Corp.	$110.50
7	1/17/2008	Baker Corp.	$11.00
8	1/17/2008	Crystal & Co.	$111.85
9	1/17/2008	Crystal & Co.	$511.00
10	1/18/2008	Baker Corp.	$711.95
11	1/18/2008	Baker Corp.	$810.00
12	1/18/2008	Baker Corp.	$310.59
13	1/18/2008	Crystal & Co.	$311.00
14	1/18/2008	Crystal & Co.	$811.50
15	1/18/2008	Ajax Inc.	$512.00
16	1/18/2008	D&D LLC	$211.00
17	1/18/2008	D&D LLC	$3,311.50
18	1/19/2008	Ajax Inc.	$410.00
19	1/19/2008	Ajax Inc.	$610.50
20	1/19/2008	Baker Corp.	$4,411.00
21	1/19/2008	Baker Corp.	$511.50
22	1/19/2008	Baker Corp.	$611.50

In SQL Server, I can do this:

SELECT COUNT(DISTINCT Customer) AS CountOfCustomer FROM Orders

Which will give me the following:

CountOfCustomer

4

In Access, if I use the Distinct predicate with the count:

SELECT DISTINCT Count(Customer) AS CountOfCustomer FROM Orders;

I get:

CountOfCustomer

22

Since Access SQL does not have the Count Distinct function, what can I do?
There are actually four different methods for simulating the Count Distinct:

1. Subqueries the FROM Clause
2. Subqueries in the Field List
3. User-Defined Function
4. Crosstab Query (this post)

Each of these methods have advantages and disadvantages, and I'll address each in turn. 

 Crosstab Query Method

A crosstab query presents aggregated data in an easy-to-understand grid. 
To create a simple Count Distinct as above, use the following SQL:

TRANSFORM Count(*) AS Cell
SELECT Count(cell) AS CountOfCustomer
FROM Orders
GROUP BY "Anything"
PIVOT Customer In (null);
To get the following result:

CountOfCustomer	<>
4

More Complex Queries

The Crosstab can also be modified to produce a customer count grouped by the OrderDate:

TRANSFORM Count(*) AS Cell
SELECT OrderDate, Count(cell) AS CountOfCustomer
FROM Orders
GROUP BY OrderDate
PIVOT Customer In (null);

OrderDate	CountOfCustomer	<>
1/17/2008	3
1/18/2008	4
1/19/2008	2

Problem: Additional Aggregation

However, in the other methods I've discussed, I was able to add additional aggregates to the query like this:

OrderDate	CountOfCustomer	CountOfOrderID	SumOfAmount
1/17/2008	3	9	$7,080.85
1/18/2008	4	8	$6,989.54
1/19/2008	2	5	$6,554.50

I can't figure out how to do that with the Crosstab query, so I'd say this method, although fast, is somewhat limited in terms of flexibility. 

Patrick insists this method has MUCH better performance than the UDF method.  Next time, I'll look at each of the methods and discuss their pros and cons, so we'll see.