Complicated query... for the children!!

[hidibiditidi]

Hi everyone,
I'm a new user to this forum and to access. I'm doing a summer research project for medical school and my mentor has given me a task which seem like it could be easy to automate in Access, but unfortunately I have no idea how to do it.

We're looking at patients who are intubated multiple times during various points in their stay in the hospital. Each patient is given a patient ID and then the intubations are listed with a start and end date.
Now my mentor has stated that each patient who is intubated for less than a day should not count as its own intubation. Rather we should just count that intubation with the one following it.
So for example:
Patient 1 intubated for 2 days
Patient 1 intubated for .1 day

We would take those two intubations and make it
Patient 1 intubated for 2.1 days and leave it as one line.

So we have converted start and end times to the days in Excel.

So there are a couple issues with this
In excel we have a list of 970 intubations and patient IDs. The intubation less than a day needs to be added to the patient with the same patient ID (so you can't always say it needs to be added to the cell above or below).

For example

Patient 1 intubated for 2 day
Patient 2 intubated for .1 day
Patient 2 intubated for 1 day
Patient 2 intubated for .2 day

in this example we would need to add the 1st intubation for patient 2 to the cell below and the 2nd intubation for patient 2 to the cell above.
so it shoudl read
Patient 1intubated for 2 days
Patient 2 intubated for 1.3 days

However it is also important to state that the patient IDs are identical between intubations (that's how we know which patient is which), so if there is a way to add everything together for a patient ID as long as it is less than 1 that would be great.

The next metric we use is called Ventilator Free days out of 28. It basically states in 28 days how many days is the patient NOT on a ventilator.

So for example:
Patient 1 intubated for 2 days
VFD would be 26

Now where it gets complicated is
Patient 1 intubated for 2 days on January 1 - A
Patient 1 intubated for 4 days on January 6 - B
Patient 1 intubated for 9 days on January 20 - C
Patient 1 intubated for 5 days on January 30 - D

Now the days that would count would All of line A, all of Line B, 8 DAYS of line C and NONE of Line D (because it is beyond the 28 day limit) .So the total number would be 2+4+8= 14.

Ok, I think that's it for now. Sorry this is so long. I'd really appeciate any help you could give me.

Thanks!

 

[David R]

Stupid question - could you just not count the .1s at all? Or do you need to count two successive .1s as 1.0?

 

[CJ_London]

It is fairly easy to do but can you provide a bit more info on what the data actually looks like - I know how it can be done but does depend on what it looks like - i.e. copy and paste a few rows from excel with names changed to protect the innocent.

Also need to clarify the rule regards part days - you said

each patient who is intubated for less than a day should not count as its own intubation. Rather we should just count that intubation with the one following it

You also said

Patient 2 intubated for .1 day
Patient 2 intubated for 1 day
Patient 2 intubated for .2 day

in this example we would need to add the 1st intubation for patient 2 to the cell below and the 2nd intubation for patient 2 to the cell above.

which is not the same, by the first rule Patient2 should be 1.1 days with .2 to be added to the following full day. - Which then raises the question for the first rule what if a patient has a part day and then leaves?

 

[hidibiditidi]

Hi David and CJ,
Thanks for the quick responses!
David, I'm not sure I understand yourq eustion. To keep us as accurate as possible my mentor does want us to include all the partial days. It's just that according to medical practice (apparently) an intubation of less than 24 hours doesn't count as its own intubation. I'm not sure what you mean by counting successive .1s as 1.0 though.

CJ,
Hi, yes I should have stated that more clearly. I meant that any intubations of less than 24 hours should be included with an intubation that is over 24 hours. If the intubation less than 24 hours is in between 2 intubations greater than 24 hours then it should be incldued in the one following it. If, however the intubation less than 24 hours is the final intubation then it shoudl be included in the previous intubation.
If there are multiple successive intubations less than 24 hours then they shoudl be added together and combined with an intubation of greater than 24 hours UNLESS there is no intubation greater than 24 hours for a specific patient in which case they are added together and left as less than 1.

I will have to get authorization from my mentor before I can post anything (HIPAA is no joke). If I can I will post it ASAP.

Thanks again!

 

[David R]

Yeah HIPAA is no joke. Maybe you can dummy up some data for us so we can see more clearly what you're talking about.

Regarding my question... I'm unclear what the difference is between your two cases. If a patient has:
6/7/13 .2
6/8/13 .1
How is that counted? 1? .3?

If a partial is always counted with the one next to it... do you mean that 6/7 and 6/8 are counted as "one" intubation, but if they're not intubated on 6/9, then 6/10 is a 'second' instance? If so why do the fractions matter at all? What if 6/7 and 6/8 are both 1s?

I think what would help is a month's worth of dummy data (for just one patient) AND what you expect the counted result to be. Include cases where there are partials next to whole-day, whole-day next to whole-day, etc...

 

[David R]

To add another question, I'm completely unclear about your 28 day metric. Is that a rolling average based on the 'first' intubation? Does B, C, D also count as a 28-day cycle, or do you start over with D because it's after the first cycle?

 

[CJ_London]

I understand your fuller explanation

I will have to get authorization from my mentor before I can post anything (HIPAA is no joke). If I can I will post it ASAP.

I was only asking for some example data! - would you ring your mechanic and ask him to fix your car over the phone - oh and not tell him what the problem is?

so if there is a way to add everything together for a patient ID as long as it is less than 1 that would be great.

The only answer I can give at the moment is Yes.

Further clarification is also required - hopefully answered in the sample data:

How do you determine the final intubation - patient discharge date? end of 28 day period?
When does the 28 day period start and end? - presumably a 4 week cycle starting from some origin date?

 

[hidibiditidi]

Hi!
Sorry for the delayed response. I actually figured out the multiple intubations thing. It was a combination of me misunderstanding it a bit and learning some excel programming on my own, but we figured it out!

Regarding Ventilator Free days that's where I'm still having a bit of a problem. To answer your question. We only look at the Ventilator Free days for the first 28 days from the point of FIRST intubation (i.e. start date in the sample data). After those 28 days VFD is no longer a reliable metric.

So I have some sample made up data which I have incldued here with some explanation.
Please let me know if oyu need more data.

Thanks!!

 

[CJ_London]

Thanks for the data but need to understand better

1. What is count of intubations? looks like count of rows for patient?
2. Are your date formats really different (i.e start is AM/PM and end is 24 hour clock?)
3. What is difference? - how is it calculated or is it a provided figure? It looks like it should be enddate-startdate but this doesn't work for line 1
4. Your sample data does not show any partial days for which treatment should be added to the next or previous per earlier discussions - has this problem gone away now and it is simply enddatetime -startdatetime?
5. Which of these columns will be produced in the data? - so far as I can see all we need is ID, startdatetime and enddatetime, everything else can be calculated - any solution will be based on this and I'd rather not provide it and you then say ah, actually it is a daily record and dates and times are kept separate (as implied in your original post).

 

[hidibiditidi]

Hi CJ,
1. Count of intubations is the number of rows the patient has it is the number of times that each patient was intubated.
2. They are not, I must have done that by accident when I formatted the cell. We use the AM/PM
3. Difference is the number of days that the patient is intubated for that particular intubation. I should have put something like "duration of intubation" The difference is what I add up within each 28 days to get the ventilator free days out of 28.
4. We still do have partial days but I thought I would make it easier by keeping it whole. The concept is still the same in that we simply use enddate-startdate.

5. For the final data we will use ID, StartDateandTime, EndDateandTime, Length of mechanical ventilation which I have already calculated separately and ventilator free days out of 28. If we need anythign else it shouldn't be hard to add those rows back into access though, should it?

Thanks for the help!

 

[CJ_London]

OK, should be fairly straightforward - I presume length of of mechanical ventilation is enddate-startdate so simply picks up part days.

As a starting point, try this query to see if it gives you the results you require.

I have used my own naming convention, you will need to substitute for your table and field names. I've highlighted an example of each in red. This query produces the same results as detailed in your sample data

SELECT DISTINCT [COLOR=red]tblIntubation[/COLOR].[COLOR=red]PatientID[/COLOR], 
(SELECT count(*) from tblintubation as tmp where patientid=tblintubation.patientid) AS IntubationCount, 
(SELECT min([COLOR=red]StartDateTime[/COLOR]) FROM tblIntubation as tmp where patientid=tblintubation.patientid) AS StartPoint, DateAdd('d',28,[startpoint]) AS EndPoint, 
(SELECT sum(iif([COLOR=red]E[/COLOR][COLOR=red]ndDateTime[/COLOR]>dateadd('d',28,startdatetime),0,iif(enddatetime>dateadd('d',28,(SELECT min(StartDateTime) FROM tblIntubation as tmp1 where patientid=tblintubation.patientid)),dateadd('d',28,(SELECT min(StartDateTime) FROM tblIntubation as tmp1 where patientid=tblintubation.patientid)),EndDateTime)-StartDateTime)) FROM tblIntubation as tmp where patientid=tblintubation.patientid) AS TotalIntubation, 
IIf([totalintubation]=0,0,28-[totalintubation]) AS VFD
FROM tblIntubation

 

[hidibiditidi]

Hi CJ,
Thank you for the code. I attempted it this morning but am getting a compile error
Here is the code that was input

SELECT DISTINCT MultipleIntubations.EpisodeID, 
(SELECT count(*) from MultipleIntubations as tmp where patientid=MultipleIntubations.EpisodeID) AS CountofStartDate, 
(SELECT min(StartDateandTime) FROM MultipleIntubations as tmp where patientid=MultipleIntubations.EpisodeID) AS startpoint, DateAdd('d',28,[startpoint]) AS EndPoint, 
(SELECT sum(iif(EndDateandTime>dateadd('d',28,startdateandtime),0,iif(enddateandtime>dateadd('d',28,(SELECT min(StartDateandTime) FROM MultipleIntubations as tmp1 where patientid=MultipleIntubations.EpisodeID)),dateadd('d',28,(SELECT min(StartDateandTime) FROM MultipleIntubations as tmp1 where patientid=MultipleIntubations.EpisodeID)),EndDateandTime)-StartDateandTime)) FROM MultipleIntubations as tmp where patientid=MultipleIntubations.EpisodeID) AS TotalIntubation, 
IIf([totalintubation]=0,0,28-[totalintubation]) AS VFD
FROM MultipleIntubations

The compile error said:
Compile Error. In query expression DateAdd('d',28,[startpoint])


I checked to make sur that both StartDateandTime and EndDateandTime were formatted as Dates.

Do you know what I am doing wrong?

 

[CJ_London]

Looks like you missed one substitution

DateAdd('d',28,(SELECT min(StartDateandTime) FROM MultipleIntubations as tmp where patientid=MultipleIntubations.EpisodeID) AS startpoint

patientID should be EpisodeID

 

[hidibiditidi]

Edit2: Ok so after examining the data this is what I've determined:
It's actually not the count of intubation that is the problem (i.e. it's happening with intubation counts of 3 or more). What seems to be the issue is the intubations which go beyond 28 days.

Here is a made up example of what is happening.

2/9/2011 12:05 3/10/2011 12:00 28.99652778
3/14/2011 16:00 4/7/2011 14:05 23.92013889


IF those were the two sets of dates for one grouping of intubation.
The rightmost column is EndDate-StartDate for each row. The value for "TotalIntubation" is -5.163194445.
Now this is NOT the value of (23.92013889-28.99652778). That is -5.076388889.

So I calculated the difference between -5.07 (the value between the 2 dates) and -5.16 (the value from the access programming) and it is 0.086805556. IF you convert that to Hours:minutes it is -2:05 (negative 2 hours and 5 inutes). If you look at the 2nd row you'll see that the difference between the TIMES only is -2:05. Which leads me to believe that for some reason it is subtracting times only for some reason.




Edit: Hey I've actually been goign through the data and the query works very well when there are two intubations, however when I have done spot checks with 3 or more intubations I have been getting different values manually. I'm going to look at the code and try to figure out what is going on exactly.



Wow. You just did in a few minutes what it took me over a week to do manually/in excel. There are a few errors, but that is where there are some errors in the data that we received from the system. I can go through and manually resolve them. Thank you very much!

 

[hidibiditidi]

Here's another example:
Patient ID Intubation Count StartDateandTime EndDateandTime Length of Intubation
1 5 12/4/2009 20:20 12/5/2009 20:10 0.993055556
1 5 12/5/2009 20:15 12/9/2009 10:00 3.572916667
1 5 12/5/2009 8:15 12/30/2009 10:00 20.07291667
1 5 12/30/2009 17:00 1/21/2010 18:00 22.04166667
1 5 1/21/2010 18:00 1/29/2010 9:30 7.645833333

The value given for TotalIntubation is 11.875 and the VFD is 16.125
The correct value should be 26.77777778

Thanks again for the help!

Edit: I have attached that I Copy pasted for easier access.

 

[CJ_London]

I've had a look at these, but there is an issue with the data in your last post:

1 5 12/4/2009 20:20 12/5/2009 20:10 0.993055556
1 5 12/5/2009 20:15 12/9/2009 10:00 3.572916667
1 5 12/5/2009 8:15 12/30/2009 10:00 20.07291667

in that first line overlaps the third line and the second line is 'fully enclosed' by the third line. Also the 3rd line days do not equate - should be nearer to 25 days and if the startdate should be 12/9/2009 then the days are 21.07!

Anyway, here is the corrected query - it should have been ignoring intubation sessions that started after the 28 day period. You can copy and paste the bit in red to cut down on 'translating' - don't forget the bracket!

SELECT DISTINCT tblIntubation.PatientID, (SELECT count(*) from tblintubation as tmp where patientid=tblintubation.patientid) AS IntubationCount, (SELECT min(StartDateTime) FROM tblIntubation as tmp where patientid=tblintubation.patientid) AS StartPoint, DateAdd('d',28,[startpoint]) AS EndPoint, (SELECT sum(iif(enddatetime>dateadd('d',28,startdatetime),0,[COLOR=red]IIf([startdatetime]>DateAdd('d',28,(SELECT min(StartDateTime) FROM tblIntubation as tmp1 where patientid=tblintubation.patientid)),0,[/COLOR]iif(enddatetime>dateadd('d',28,(SELECT min(StartDateTime) FROM tblIntubation as tmp1 where patientid=tblintubation.patientid)),dateadd('d',28,(SELECT min(StartDateTime) FROM tblIntubation as tmp1 where patientid=tblintubation.patientid)[SIZE=5][COLOR=red][B])[/B][/COLOR][/SIZE],EndDateTime)-StartDateTime))) FROM tblIntubation as tmp where patientid=tblintubation.patientid) AS TotalIntubation, IIf([totalintubation]=0,0,28-[totalintubation]) AS VFD
FROM tblIntubation

As a bonus, this query will highlight all the overlaps (you'll need a unique ID in your table for it to work)

SELECT tblIntubation.*, tblIntubation_1.*
FROM tblIntubation INNER JOIN tblIntubation AS tblIntubation_1 ON tblIntubation.PatientID = tblIntubation_1.PatientID
WHERE (((tblIntubation_1.StartDateTime)<[tblintubation].[enddatetime]) AND ((tblIntubation_1.EndDateTime)>[tblIntubation].[StartDateTime]) AND ((tblIntubation_1.ID)<>[tblIntubation].[ID]));

With regards your slightly earlier post referring to this data

3/14/2011 16:00 4/7/2011 14:05 23.92013889

The difference between the hours is 1 hr 55 mins, not 2 hrs 5 mins

I've just run those figures through the query and it returns 0, which I would expect since the first intubation is over 28 days so is discounted.

However hopefully the fix above will resolve this one as well.

Anyway, I need to get back to my sparkgraphs!

 

[hidibiditidi]

Hi CJ,
I had a chance to input the code and check the restuls and I think it works. Thanks so much for all the help. I will let you know if I find any errors.