Running count of distinct values in Access

Question

I have data stored as below in an MS Access database:

Date         User
20090101     1001
20090101     1002
20090102     1001
20090103     1001
20090103     1003

I'm attempting to create a query which shows the daily running count of unique users. For example:

Date        Daily Count   Unique User Running Count
20090101    2             2
20090102    1             2
20090103    2             3

What's the best way to achieve this?

Answer 1

Your query will look something like this...can't test it without the data though:

SELECT Date, Count(Date) As [Daily Count], Count(User) As [Unique User Running Count]
FROM TableName
GROUP BY Date

Answer 2

It's not exactly the same scenario, but this questions answers should give you a starting point.

Answer 3

In most SQL implementations you could select using the aggregate function count(distinct user). But Access doesn't support that construct. I think the best you could do is to select the distinct values in a subquery and count them.

I was going to write a query but this link seems to do a good job.

HTH Tom