Count of records between 2 date fields (1 Viewer)

I'm wondering if it is possible to create a query off of a table as such:
Table fields:
PatientID
StartDate
EndDate

How do I create a query that will yield the number of PatientID's for all dates in between Startdate and EndDate for ALL dates, say in 2017.
(I.e, I want to know the # of patients that were here on a daily basis, for each day - so would include the startdate, all dates in between, and the end date).

Date #PatientID
1/1/17 - 3
1/2/17 - 4
1/3/17 - 2
etc

I hope I am explaining this correctly!

Thank you for your help!
Michele

How do I create a query that will yield the number of PatientID's for all dates in between Startdate and EndDate for ALL dates, say in 2017.

Click to expand...

First you will need a data source (Table or Query) with all dates in 2017. Do you have that? Let's say it looks like this:

tblReportDates
ReportDate
1/1/2017
1/2/2017
...
...
12/31/2017


Second you would need a sub-query to determine if a patient was in for a particular date. That query would be a Cartesian Product query, where you don't JOIN your two tables, but instead use a WHERE clause to narrow the results:



Then you would build another query using the above query and tblReportDates. You would LEFT JOIN to show all data from tblReportDates and just those matching in the query. GROUP BY ReportDate and COUNT by PatientID.

Counting patients seen during a period is different from counting visits. Since you want a count by day, I'm assuming you want to count OfficeVisits. SQL has a specific syntax that allows you to count unique patients in this context but Access does not and so you would need to use either nested queries or a subquery to first get a list of distinct patientIDs.

Seect VisitDate(), Count(*) as PatientsSeen
From tblOfficeVisits
Where VisitDate Between Forms!yourformname!txtStartDate and Forms!yourformname!txtEndDate
Group By VisitDate

On the form, you can have the user enter a start and and end date or you can make a default that counts for the current year or month if one of those is the most common option.

Wonderful! Thank you Plog! I was on the right track creating the separate dates table, but needed the extra help to finalize. Also had to modify for dates not yet discharged to be counted.
Really appreciate your help.