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:
In Access, if I use the Distinct predicate with the count:
SELECT DISTINCT Count(Customer) AS CountOfCustomer FROM Orders;
I get:
Since Access SQL does not have the Count Distinct function, what can I do? This sample shows you.
Download Access 2000+: http://www.rogersaccesslibrary.com/forum/uploads/5/CountDistinct.zip - CountDistinct.zip
------------- Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<
|