Count unique users by month

[TSO456]

Hello,
I have an access table that shows the users and the number of times they have visited my site by month. So picture a grid where all users are on the left and the months on the top and the number of hits/visits to my site in the middle.

I would like to create a query that shows me the number of unique users every month. So for a period of six months how many unique users visited my site. It is important that I see the data by month. I am only interested in the new users. By the way some users dropoff for a month or two and then come back. I am not inerested in capturing those.
Thank you
Jill

 

[PHV]

Can you please post the schema of your table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TSO456]

The table looks like this:

Users ID Jan 04 Feb 04 March 04 April 04 etc..
A022212 20 10 0 25
A322222 10 15 10 50
A000012 3 1 5 6
etc..


Thanks
Jill

 

[JonFer]

It will be much easier to do this with SQL if your table is normalized:

  Users   Date     Visits
   A1    1/1/2004    20
   A1    3/1/2004    10
   ...

Can you change your table design?

John

http://www.interautomateconsulting.com

 

[TSO456]

Yes, I can change the table to look like the above. After I make that change what is the SQL that I need to use to achieve my results?
Thanks
Jill

 

[JonFer]

I'm not sure exactly what you want but this should help:

Select User, Format(Min(VisitDate),"yyyy-mm") as VisitMonth
From YourTable
Group By User
Having Min(VisitDate) => [Enter earliest visit date]

Don't add a WHERE clause to filter by date because you need the early records to get just the new people.

John

http://www.interautomateconsulting.com