Retrospective Patient Census

[annemu]

Hi all,
Perhaps someone may help me. We run a database that records patient data for people who are admitted to a critical care unit. We have a staff ratio that allows us to care for 9 patients, but we do have 12 beds, so there are commonly more than 9 patients within the unit. I have been asked to see if we can find out how often this occurs.
We collect admission time and Discharge time on all patients. Generally, we would have an ICU census time of midnight or midday, and at these times if there are more than 9 patients, then it is recorded at that time. I wanted to query the tables retrospectively to determine how many days the number of patients exceeded 9. Any ideas?

 

[kballing]

I think I've got an idea that might work

I did something similar with patient lab results once.

first, heres a link that explains the concept:
http://www.access-programmers.co.uk/forums/showthread.php?t=185371

Ok, create a dummy table with all the dates you are interested in, we'll call it DateTbl. It would have data like this

1/1/2010
1/2/2010
1/3/2010
1/4/2010
etc. . .

Then make a new query that will create a new table of patient/date combinations
for example if patient 12345 admitted on 1/1/2010 and discharged on 1/3/2010, then it would create 3 records

patient | date
12345, 1/1/2010
12345, 1/2/2010
12345, 1/3/2010

the query might look something like this, assuming your original table is PatientTbl, it creates a new table PatientDates

Select PatientTbl.PatientID as PatientID, DateTbl.Date as Date
Into PatientDates
From PatientTbl, DateTbl
Where DateTbl.Date between PatientTbl.AdmitDate -1 and PatientTbl.DischargeDate +1;

Then you can query your new table to see how many patients on each day

Select Date, count(PatientID)
From PatientDates
Group By Date;

I hope that makes sense. I'm no Access pro.

 

[annemu]

Thanks a million KBalling - I ran a query like you suggested, but found that I had too many records returned for some dates (up to 20) and we only have 12 beds. It looks more reasonable now, after I modified the criteria to read only Where DateTbl.Date Between AdmitDate and DischargeDate. That seems to have worked well. Hopefully I am correct - why do you suggest the +1 and -1 ?

 

[kballing]

I don't remember why I included the +1 and -1, I wasn't concentrating very hard when I replied.

 

[boblarson]

I don't remember why I included the +1 and -1, I wasn't concentrating very hard when I replied.

Probably to take into account TIME instead of just dates.

 

[kballing]

yeah, that's it, except you'd actually want to do admit date/time + 24-admit time and dishcarge date/time - discharge time, and if you'r going to do that, you'd might as well just use the date only.