count distinct number of visits

[topwaya]

I have the following query that counts the total number of visits to a facility. I would ALSO like to count the total number of UNIQUE visits.

For instance, total visits might 100, it is how times anyone has visited.

Unique visits might be 42, it is how many unique individuals visited.

Here is my SQL - how do I count distinct number of visits? I haven't been able to figure it out. the tblFCUtil.PID needs to also be distinctly counted to get what I want.

SELECT tblLoc.LocName, Count(tblFCUtil.PID) AS NumberOfFCVisits, tblFCUtil.WOD, tblFCUtil.WOTme, tblFCUtil.WOLoc, tblFCUtil.PID
FROM tblLoc INNER JOIN tblFCUtil ON tblLoc.LocID = tblFCUtil.WOLoc
GROUP BY tblLoc.LocName, tblFCUtil.WOD, tblFCUtil.WOTme, tblFCUtil.WOLoc, tblFCUtil.PID, tblLoc.LocID
ORDER BY tblLoc.LocName;

Thanks for any help!

 

[Hmadyson]

Ok, can I assume that WOD and WOTime are always unique and you really only care how many people are visiting any one location (even though they could come many times with many WOD and WOTme).

Individual People (qryPersonAtLoc):

Select Distinct tblLoc.LocName, tblFCUtil.PID FROM tblLoc INNER JOIN tblFCUtil ON tblLoc.LocID = tblFCUtil.WOLoc;

Distinct Visits:

Select a.LocName, Count(*) as [Distinct Visits] from qryPersonAtLoc Group By a.LocName;

You can probably do it in one query, but here I am doing it in 2.

 

[topwaya]

Thank you -
This works if I type in the Location Name each time a run the query. I need all of the locations to show how many total and unique visits each had in one query, and in one run of the query so I can put the query into a report.
Thanks again for any help!

 

[lespaul]

Do you mean you get prompted for a value?

You don't have a WHERE clause in your statement above, have you added one? Could you post your exact SQL statement?

leslie

 

[topwaya]

I used the exact SQL that was given by Hmadyson, above.
And yes, the second query that he gave the SQL for prompts me for a location name, and it works that way, but I only get results for one location, the prompted location.

But what I'm actually trying to figure out how to do is to get the TOTAL VISITS, the UNIQUE USERS, and display them by each LOCATION for each month in a report.

So that:
January: LOC1 had 100 TOTAL VISITS and 42 UNIQUE VISITS (or maybe I should say unique users?)
LOC2 had 80 TOTAL VISITS and 30 UNIQUE VISITS

For all the locations, and for all the months in the year.
Thanks so much for any help!