distinct users per week

[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

 

[Michel Walsh]

Hi,


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


Hoping it may help,
Vanderghast, Access MVP

 

[John Viescas]

SELECT username, Count(*) As usercount
FROM
(SELECT DISTINCT username, Format([date], "ww") As Weekno
FROM MyTable) As DU
GROUP BY username;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Liron]

Sure helped

Thank you very much

 

[Michel Walsh]

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

Sure helped

Thank you very much

Hi,


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


Hoping it may help,
Vanderghast, Access MVP

 

[Liron]

Hi,

I figured that the "group by" part was missing.

Thank you again for your great help