Roger's Access Library Homepage
Forum Home Forum Home > Roger's Access Library > Roger's Download Samples
  New Posts New Posts RSS Feed - CountDistinct.mdb (Intermediate)
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

>
Want a good read? Try The Summer of His Life (available on Amazon!)

CountDistinct.mdb (Intermediate)

 Post Reply Post Reply
Author
Message Reverse Sort Order
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 20 Feb 2008
Status: Offline
Points: 261
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Topic: CountDistinct.mdb (Intermediate)
    Posted: 06 Jun 2012 at 7:26am

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?  This sample shows you.

Download Access 2000+: CountDistinct.zip

Consider donating $1 per sample downloaded, find out why here.<
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.