Using Date Fields in a Table as Criteria for a Count Query

[Guest]

I have a table that includes the following fields: AccountNumber, AdmitDate,
and DischargeDate.

I need to build a query that allows entry of a date range (for example,
4/1/07 to 4/30/07) and returns a count of the total number of AccountNumbers
for each day in that range for which that date falls between AdmitDate and
DischargeDate.

Basically, I'm trying to count the number of patients in the hospital on
each date in the range entered.

Thanks,

Todd

 

[Guest]

Create a table named CountNumber with a field named CountNUM and fill with
integers from zero to your maximum patient stay (100 maybe).
Use this query changing the table name from jtoddspradlin to what your table
is called.
SELECT DateAdd("d",[CountNUM],[AdmitDate]) AS [Hospital Days],
Count(jtoddspradlin.AccountNumber) AS [Number of patients]
FROM jtoddspradlin, CountNumber
WHERE (((DateAdd("d",[CountNUM],[AdmitDate]))<=[DischargeDate]) AND
((jtoddspradlin.AdmitDate)>=[Enter start date]) AND
((jtoddspradlin.DischargeDate)<=[Enter end date]))
GROUP BY DateAdd("d",[CountNUM],[AdmitDate]);