By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

How to count unique users and also number of visits in a report

P: 4
I'm trying to get a report by location that would list the number of visits to the location for the month but also count the number of unique visitors to that location.

I have a table of data with Location, VisitorId, and DateOfVisit. I then have a query to filter by a single month and then base my report on that query.

In my report I have a group header for the location and I can do a count of the VistorId or DateOfVisit fields to get a the number of visits to the location but my problem is that some of the visitors may come more than once per month and I want to know how many unique visitors came during the month.

I can run two separate queries with one grouping by VisitorId to get my number of unique visitors but can I get the same result from a single report based on the source query described above so it would look something like:

==Location Group Header==
[Location]_______________=Count([VisitorId])____*Unique Visitors to go here*
Feb 4 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 446
I'm trying to get a report by location that would list the number of visits to the location for the month but also count the number of unique visitors to that location.

I have a table of data with Location, VisitorId, and DateOfVisit. I then have a query to filter by a single month and then base my report on that query.

In my report I have a group header for the location and I can do a count of the VistorId or DateOfVisit fields to get a the number of visits to the location but my problem is that some of the visitors may come more than once per month and I want to know how many unique visitors came during the month.

I can run two separate queries with one grouping by VisitorId to get my number of unique visitors but can I get the same result from a single report based on the source query described above so it would look something like:

==Location Group Header==
[Location]_______________=Count([VisitorId])____*Unique Visitors to go here*
Hi
I don't know why anybody has not tried to help with this one for two weeks. Usually there is a response within a couple of days.

I think you are more than half way to your solution but need to read-up on UNION QUERY

You have seemingly already created you two queries that produce the right numbers; one the TotalVisits and the other UniqueVisitors for each loaction. If you can go into SQL View for each of these from the Query Design screen then cut and paste the SQL code into this thread I will show how to joint them to produce the result you require

S7
Feb 20 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.