Persons in a department

[Doc94]

I need some help writing a query. I need to know how many patients are in my department at a given day and time. Currently, my database records the time they are admitted and the time they are discharged. Arrival time is HospitalArrivalTime which gives date and time. It is shortened using "HATTime: TimeValue([HospitalArrivalTime])". Disposition time (time they leave) is DispositionTime. I can calculate the length of stay with [DispositionTime]-[HATTime]. I can count how mny patients are admitted at a particular time and how many are discharged at a particular time. But it is between thos times I have not figured out a calculation.
If any other info is needed let me know.

 

[PHV]

Something like this ?
WHERE [given day and time] Between HospitalArrivalTime And DispositionTime

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Doc94]

I have tried where hospitalarrivaltime < or = clock time and disposition time > clock time but it does not work. To expand a bit:

12am 1am 2am 3am...............
2/1/05 Count count count count
2/2/05 count count count
2/3/05

Along these lines, if a patient arrives at 12 am and is dischared at 2:30 am they would be counted in the 12am, 1am and 2am period. The next patient arriving at 1am and discharged at 4am would show up as being counted for 1-4am. So at 12 am the count would be 1, 1am and 2 am would have a count of 2 and 3am would have a count of 1. Keeping in mind this is for 60-70 visits a day and not just the 2 outlined here.

 

[PHV]

Have you considered a CrossTab query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Doc94]

I have tried a cross tab query but again it is a matter of finding the positives- patient is in the department at a particular clock time between HATTime and DispositionTime, then count them, otherwise do not. I have been trying to think of and an IIF else statment but drawing a blank.