Have Geek Brain

Counting distinct values ​​in Access

I have data stored below in MS Access database:

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

I am trying to create a query that shows the daily launches of unique users. For instance:

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

What's the best way to achieve this?

+1


a source to share


3 answers


In most SQL implementations, you can choose to use the aggregate function counter (single user). But Access doesn't support this construct. I think the best thing is you can select different values ​​in the subquery and count them.

I was about to write a request, but the link seems like a good job.



NTN Tom

+3


a source


Your request will look something like this: I can't check it without data:



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


a source


I did it! Simple solution is best: no SQL coding required.

enter image description here

In Access, Query Design,

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


a source






More articles:


All Articles