CODEWITHSUNDEEP
ms-access
Paul Ellery
Paul Ellery

Reputation: 1745

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?

Upvotes: 1

Views: 19928

Answers (3)

russ
russ

Reputation: 1

I did it! A simple solution is the best: no SQL coding needed.

enter image description here

In Access, Query Design, 

Column 1 = 
Field=Date
tablename=yourname
Total=Groupby
Column2 =
Field=Date
Table=yourname
Total=Count

Upvotes: 0

Tom
Tom

Reputation: 262

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

Upvotes: 3

Justin Niessner
Justin Niessner

Reputation: 245479

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

Upvotes: 0

Related Questions