Query

[Ray Slayton, RN]

I have a large database of patient specific information that I need to query.
I have times admitted and discharged into the unit stored in 10/2/2008
11:05:00 AM type format. What I need is to figure out how to write a query,
macro or something that I could run to tell me how many patients are in the
unit at any time of the day or night on an hour by hour basis for each day.
Any suggestions?

 

[Michel Walsh]

In SQL view:



SELECT DateValue(yourDateTimeField), Hour(yourDateTimeField), COUNT(*)
FROM yourTableName
GROUP BY DateValue(yourDateTimeField), Hour(yourDateTimeField)


You can switch back in graphical view to see how you could have make it,
graphically.

Vanderghast, Access MVP

 

[tedmi]

Michel's query counts how many patients were admitted hour by hour, or how
many were discharged, depending on whether yourDateTimeField is TimeIn or
TimeOut. It does not provide for counting those who were admitted some hours
ago and are not yet discharged. To get the number in the clinic, for any
given Day & Hour, count how many were admitted prior to or on this day/hour,
subract the count of those discharged prior to this day/hour. Try this: (NOT
TESTED!):

Create qHourSlot:
SELECT TimeIn, TimeOut, Format(DateValue(TimeIn) & " " & Hour(TimeIn) &
":00:00","General Date") AS HourSlot FROM YourTable

Create qAdmit:
SELECT Count(*) AS Ins FROM qHourSlot
WHERE TimeIn<=HourSlot
GROUP BY HourSlot

Create qDischarge:
SELECT Count(*) AS Outs FROM qHourSLot
WHERE TimeOut < HourSlot
GROUP BY HourSlot

Final report query:
SELECT Ins-Outs AS InDept
FROM qAdmit INNER JOIN qDischarge on qAdmit.HourSlot=qDischarge.HourSlot

This gives the count of patients in the clinic during any part of the hour,
only for hours in which admissions occur. Hours without counts have the same
count as the previous hour. It also handles admit & discharge within the same
hour.

Ray, if you need more help, please send a selection of TimeIn, TimeOut
data(WITHOUT patient or clinic info!) in an Excel file to ted at mirecki dot
us.

Good luck

 

[tedmi]

Sorry, correction to qAdmin, qDischarge:
add the field HourSlot to the SELECT list of each of these queries.

For the final query, add qAdmin.HourSlot to the SELECT list.

qHourSlot is a "helper query" that is not really necessary, but makes the
writing and reading of the other queries much easier.