COUNT (DISTINCT)?

[ceh4702]

I have a table that is named ORDER and it has attributes
Number,CustName,SalespersonName,Amount.
I tried the following queery:

SELECT COUNT (DISTINCT CustName)
FROM ORDER;

Of course My Access 97 will not accept this.
How do you count the customer's names without counting the duplicates from the table?

My BOOK A GUIDE TO SQL, BY Philip J. Pratt, says this will work???
If you do not like my post feel free to point out your opinion or my errors.

 

[tlbroadbent]

Try this.

Select Count(*) As Cnt
From (Select Distinct CustName From ORDER) As q;

It appears that "A GUIDE TO SQL" by Philip J. Pratt focuses on ANSI SQL. Access is not totally compliant with the ANSI standard. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.