Access 2003 - count unique values

Question

How to count unique values in Access 2003? When i write sth like this:

SELECT DISTINCT Customer FROM CustomersTable;

I've got as result unique customers, but how count them if this code doesn't work:

SELECT COUNT(*) 

FROM (SELECT DISTINCT Customer FROM CustomersTable )

(it causes error: "The Microsoft Jet database engine cannot find the input table or query 'SELECT DISTINCT Customer FROM CustomersTable'. Make sure it exists and that its name is spelled correctly")

Example of database

--Customer -- Address -- 
X               NY 
X               OR 
Y               AR 
Z               WA 

And I'd like to have as result 3 (three unique Customers.)

Answer 1

You need to give your subquery a name:

SELECT COUNT(*) 
  FROM (SELECT DISTINCT Customer FROM CustomersTable) AS T

Answer 2

Try this:

Select 
    Customer, 
    count(Customer) as count
from
    CustomersTable
group by
    Customer

Alright, You should try this now:

SELECT COUNT(DISTINCT Customer) FROM CustomersTable;

Hope this helps.

Answer 3

Edit (Query Corrected)

This worked for me in Access 2007

SELECT Count(*) AS NumCustomers
FROM 
(
    SELECT Distinct CustomerAddress.Customer
    FROM CustomerAddress
)  AS DistinctCustomers;

Answer 4

I know this question was probably abandoned a long time ago but just for anyone who is having this issue, as I was having errors using a C# application to do a similar task and found refuge here: http://www.geeksengine.com/article/access-distinct-count.html

This helped me a lot and if broken, gave the following query structure

    SELECT COUNT(columnName) as num_ofColumnNameEntries
    from
       (
        SELECT DISTINCT columnName FROM TableName
       )