select queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
SELECT count(personName),
pageName
FROM yourTable
WHERE visitDate BETWEEN firstDate AND secondDate
GROUP BY pageName
 
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
'====================================================
 
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#)
)
)
 
Back
Top