Count of records between 2 date fields

[miacino]

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

 

[plog]

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.

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:

SELECT ReportDate, PatientID
FROM tblReportDates, YourTable
WHERE ReportDate >= StartDate AND ReportDate<=EndDate

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.

 

[miacino]

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.