distinct users per week

  • Thread starter Thread starter Liron
  • Start date Start date
L

Liron

Hello,

I'm trying to create a query that selects the distinct number of users which
are in a certain table for that same week.

for example, my table looks like this:

username date
----------- ------------
user1 1/1/2003
user2 1/2/2003
user3 1/7/2003
user2 1/8/2003
user4 1/8/2003
user2 1/9/2003

Lets assume that 1/1/2003 is the first day of the week, then I'd expect this
output

week count
1 3
2 2

In the first week there were only 3 distinct users, on the second week there
were 3 user records but multiple records for the same user, so only 2
distinct users for that week.

I also have a primary key in the table, lets call it index and assume it's
autoincrement and describes the record number in the table.

Any ideas?

Thank you
 
Hi,


SELECT WeekOf, COUNT(*) As CountOfDistinctUsers
FROM ( SELECT DISTINCT User, DatePart('ww', [date] ) As WeekOf FROM
somewhere )


Hoping it may help,
Vanderghast, Access MVP
 
Hi,


I missed the final Group By, so, here in full:

SELECT WeekOf, Count(*) as CountOf
FROM ( SELECT DISTINCT User, DatePart("ww", [date]) As WeekOf
FROM somewhere)
GROUP BY WeekOf



It works only from Access 2000 and more recent (Jet 4.0).


Vanderghast, Access MVP


Liron said:
Sure helped

Thank you very much

Michel Walsh said:
Hi,


SELECT WeekOf, COUNT(*) As CountOfDistinctUsers
FROM ( SELECT DISTINCT User, DatePart('ww', [date] ) As WeekOf FROM
somewhere )


Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Back
Top