Query: COUNT in Access To Only Count Unique Values

Question

I have a table like so:

Customer  Purchase Date  Product
Frank     7/28/2015      Hammer 
Bob       7/29/2015      Shovel 
Bob       7/29/2015      Pickaxe 
Bill      7/30/2015      Pliers

The Purchase Date field records a new entry for every purchase. So, if in one visit a customer purchases four items, my database creates four entries each with the same date.

I'm trying to write a query that displays the numbers of visits for each customer. Output like so:

Frank  1
Bob    1
Bill   1

But when I use the COUNT function on the date in my query, it returns:

Frank  1
Bob    2
Bill   1

I want my query to only count unique dates, but the COUNT function doesn't work. Everywhere I read, it also says that the SQL COUNT (Distinct) doesn't work in Access. Access help says that if I set the Query Properties to Unique Values "Yes", it should only return unique values, but it doesn't work. I tried Unique Record "Yes" also, but that didn't work either.

Please help! Thanks!

dev1998 dev1998 · Accepted Answer · 2015-07-30T21:51:06

Try this:

select Cust, count(cust) as CustomerCount
from (Select Distinct Table1.Customer as cust, Table1.PurchaseDate
from Table1)
group by cust

Query: COUNT in Access To Only Count Unique Values

1 Answers