Q:

Running count of distinct values in Access

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?

A:

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

