Count of Patients by City within date range

[David Brown]

I have a database with patient data and visit data. I would like a report
that lists the number of patients by city that visited between a certain
date range.

It's very easy to show the total number of patients by city using the
patient table. And, its very easy to show the total number of visits by city
using the visit table. However, I can't seem to get the results I'm looking
for. Any help would be greatly appreciated.

An example of the output I'm looking for would be:
For patient visits between 1/1/2008 and 12/31/2008

Chicago 12 patients
Dallas 24 patients
Nashville 18 patients

What's throwing me off is that many patients have more than one visit in the
time frame and I don't want to count them more than once.

Thanks,

David

 

[Duane Hookom]

I would first add a couple text boxes to a form for the user to enter the
start and end dates:
Forms!frmDates!txtStart
Forms!frmDates!txtEnd

Then create a totals query that groups by city and patient and filters to
the date range
SELECT PatientID, City
FROM ....
WHERE VisitDate Between Forms!frmDates!txtStart AND Forms!frmDates!txtEnd
GROUP BY PatientID, City;

You can then build another query based on this first one like:
SELECT City, Count(*) as NumOfPatients
FROM qgrpPreviousQuery
GROUP BY City;

Use this final query as the record source of your report.