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

J

jtoddspradlin

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
 
K

KARL DEWEY

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]);
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top