MS ACCESS count/sum number of rows, with no duplicates

Question

I have the following table which I need to count the total number of rows without including any duplicate records.

CustomerID
test1
test1
test2
test3
test4
test4

As you can see, the total number of rows is 6 but there are two test1 and two test4, and I wish the query to return 4. IOW, I want to count unique values in CustomerID.

I've tried sub query but didn't get it to work for me.

-- Update 27/06/2012 --

Thanks, both worked for me:

1. SELECT COUNT(*) FROM ( SELECT CustomerID FROM TheTable GROUP BY CustomerID ) as
2. SELECT COUNT(*) FROM ( SELECT DISTINCT CustomerID FROM TheTable ) AS sub;

I now need a query to keep only those CustomerID which were added today, then count them.

For example,

CustomerID DateAdded
test1 25/06/2012
test1 25/06/2012
test2 26/06/2012
test3 27/06/2012 - Today
test4 27/06/2012 - Today
test4 27/06/2012 - Today

then it needs to return 3.

I've tried adding in the following query into the two solutions provided, but couldn't get either of them to work/return the value I want.

HAVING (([TheTable].DateAdded)=Date());

Solution 1

The second suggestion in dbaseman's answer looks correct to me. This alternative should also work.

SELECT COUNT(*)
FROM
    (
        SELECT DISTINCT CustomerID FROM TheTable
    ) AS sub;

I don't know whether it offers any advantage over dbaseman's GROUP BY. If you decide to test both approaches, please add a comment to tell us what you find.

Based on your comment, you want to evaluate "only those records that were added today". Since you didn't give us enough information about your table, I will pretend it includes a Date/Time field named date_added. And each time you add a record you store the current date, with the time component as midnight, in that field. In that case you can add a WHERE clause to the subquery to ask the db engine to consider only those rows where date_added is equal to today's date. The Date() function will give you today's date, with midnight as the time component.

SELECT COUNT(*)
FROM
    (
        SELECT DISTINCT CustomerID FROM TheTable
        WHERE date_added = Date()
    ) AS sub;

Once you replace date_added with your field name, that should work provided my assumptions were correct. However if your date_added values include any time component other than midnite, you will have to revise the WHERE clause to capture all Date/Time values from today.

WHERE date_added >= Date() AND date_added < (Date() + 1)

Solution 2

This would work in SQL, I think it should also in Access:

SELECT COUNT(DISTINCT CustomerID) FROM TheTable 

Edit: Per Jeff in comments, Access doesn't support the above syntax.

This should work for sure though:

SELECT COUNT(*) FROM ( SELECT CustomerID FROM TheTable GROUP BY CustomerID ) a

To restrict by date

SELECT COUNT(*) FROM ( 
    SELECT TheTable.CustomerID FROM TheTable GROUP BY CustomerID 
    WHERE TheTable.DateAdded >= Date()
) a