select queries

T

Taisiya

Hello, everybody!
I have a table which consist three fields:
1) Name of the person
2) name of the page visited ( just two variants, people can shoose just from
these two pages)
3)The day, when the person visited the pages (the dd/mm/yyyy format)
One person can visit the page more than one time in one day.

I need to know the amount of new people each month for each separate page.
Any ideas?
Would sooooooo appreaciate your help.
thanks, taisiya
 
J

Jason Lepack

SELECT count(personName),
pageName
FROM yourTable
WHERE visitDate BETWEEN firstDate AND secondDate
GROUP BY pageName
 
J

John Spencer

Getting a count of first time visitors for one specific month would
involve a sub query or a nested query. Assuming your field and table
names have no spaces or other non-number or non-letter characters you
could do that all in one as follows.


SELECT PageName, Count(PersonName) as UniquePersonCount
FROM
(SELECT Distinct PersonName, PageName
FROM YourTable
WHERE VisitDate Between #11/1/2006# AND #11/30/2006#)

If you are going to try to do this for more than one month at a time it
becomes more complex.

Rather than try to develop that SQL now, I'll wait and see if the above
is sufficient to answer your question.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
J

John Nurick

If Taisiya needs the numbers of people who have never previously visited
that page, there's a bit more complication. Maybe

SELECT PageName, Count(PersonName) as UniquePersonCount
FROM
(SELECT DISTINCT A.PersonName, A.PageName
FROM YourTable As A
WHERE VisitDate Between #11/1/2006# AND #11/30/2006#
AND A.PersonName NOT IN
(SELECT DISTINCT B.PersonName
FROM YourTable AS B
WHERE (B.PageName = A.PageName)
AND (B.VisitDate < #11/1/2006#)
)
)
 
Top