Count instances of date ms access query

[scottb]

Hello. Thank you very much for creating this lounge!
I have query challenge that I will attempt to explain. We track patient admission and discharge dates. Three fields: patientID, admission_date, discharge_date. I would like to create a query that counts patient readmissions based upon 7, 14, and 30 day increments. Ideally I would like the results to include the patient ID, admission_date, discharge_date, and count of any readmissions within 7, 14, or 30 days. This is determined by using the days between a visit's admission_date and the last visit's discharge date. I have spreadsheet showing what this would look like. Each visit can have a one or more 7, 14, or 30 day readmissions. Hope this makes at least some sense. It's a bit hard to describe. The purpose is to identify possible infection/complications that frequently correspond to quick readmissions. This is currently being done manually and takes a very long time. Any help would be greatly appreciated. Thank you all! -Scott

[HansV]

Hi Scott,

Welcome to Eileen's Lounge!

Assuming the table is named tblAdmissions, you could create a query with the following SQL:

SELECT tblAdmissions.PatientID, tblAdmissions.Encounter, tblAdmissions.Admitted, tblAdmissions.Discharged, Val(DCount("*","tblAdmissions","PatientID=" & [PatientID] & " And Admitted Between #" & Format([Discharged],"mm/dd/yyyy") & "# And #" & Format([Discharged]+7,"mm/dd/yyyy") & "#")) AS 7Day, Val(DCount("*","tblAdmissions","PatientID=" & [PatientID] & " And Admitted Between #" & Format([Discharged],"mm/dd/yyyy") & "# And #" & Format([Discharged]+14,"mm/dd/yyyy") & "#")) AS 14Day, Val(DCount("*","tblAdmissions","PatientID=" & [PatientID] & " And Admitted Between #" & Format([Discharged],"mm/dd/yyyy") & "# And #" & Format([Discharged]+30,"mm/dd/yyyy") & "#")) AS 30Day
FROM tblAdmissions;

See the attached sample database (Access 2000 format, zipped).

Admissions.zip

[Mark L]

I don't know if it is possible (I assume it is), but your query will produce erroneous results if a patient is admitted and discharged on the same day. Example:

PatientID Encounter Admitted Discharged 7Day 14Day 30Day

1234500 1 04/01/10 04/02/10 1 1 1
1234500 2 04/02/10 04/02/10 1 1 1

[HansV]

You're correct. This can be avoided by changing the three occurrences of

Admitted Between #" & Format([Discharged],"mm/dd/yyyy")

to

Admitted Between #" & Format([Discharged]+1,"mm/dd/yyyy")

Thanks!

[Mark L]

Actually, that won't work either. The #1 Encounter would then not show Encounter #2, since its Admit Date was the same as the Discharge Date for #1. I think you would have to add something to the Where clause of your DCount, something to effect of " AND EncounterNo <> " & EncounterNo.

[HansV]

I'm not sure how realistic it'd be to be admitted, discharched and readmitted on the same day, but try this version:

SELECT tblAdmissions.PatientID, tblAdmissions.Encounter, tblAdmissions.Admitted, tblAdmissions.Discharged, Val(DCount("*","tblAdmissions","PatientID=" & [PatientID] & " And Encounter>" & [Encounter] & " And Admitted Between #" & Format([Discharged],"mm/dd/yyyy") & "# And #" & Format([Discharged]+7,"mm/dd/yyyy") & "#")) AS 7Day, Val(DCount("*","tblAdmissions","PatientID=" & [PatientID] & " And Encounter>" & [Encounter] & " And Admitted Between #" & Format([Discharged],"mm/dd/yyyy") & "# And #" & Format([Discharged]+14,"mm/dd/yyyy") & "#")) AS 14Day, Val(DCount("*","tblAdmissions","PatientID=" & [PatientID] & " And Encounter>" & [Encounter] & " And Admitted Between #" & Format([Discharged],"mm/dd/yyyy") & "# And #" & Format([Discharged]+30,"mm/dd/yyyy") & "#")) AS 30Day
FROM tblAdmissions;

New sample database attached.

Admissions.zip

[scottb]

Hans,Mark,
I am going to run a data set against this but it looks like it is calculating correctly. Unfortunately we do see readmits (usually post operative) on the same day as a discharge...someone goes home and bleeding/infection/complications sets in). This is wonderful and I really appreciate your help. I will loop back to confirm results of our test.
Thanks again.

[scottb]

Would the same functionality be possible without an encounter field? I was informed that not all our hospitals utilize an encounter number within their data, just raw admission and discharge dates. Thank you and sorry for missing that earlier.

[HansV]

If the table has an AutoNumber field, you could use that instead of the Encounter field.
Otherwise, does the table contain the time of admission in addition to the date?
If not, the query can't distinguish between admissions on the same day.

[scottb]

I am going to find out if we can obtain the date and time of the admission from the source systems. Thank you again for your help.

[HansV]

If not, the query will still be usable - more or less. It'll just give a count of one too high if someone is readmitted the same day.

[scottb]

Hi again. I was able to get admission and discharge times added to the data file (attached). Will the adddition of this information be sufficient to count the readmissions (if discharged and readmitted on the same date)? The times are identified in separate fields....do they need to be combined with the date field to ensure accuracy? Thank you again for your help. - scott

[HansV]

Try this:

SELECT tblAdmissions.PatientID, tblAdmissions.AdmittedDate, tblAdmissions.AdmittedTime, tblAdmissions.DischargedDate, tblAdmissions.DischargedTime, Val(DCount("*","tblAdmissions","PatientID=" & [PatientID] & " And AdmittedDate+AdmittedTime/10000 Between #" & Format([DischargedDate]+[DischargedTime]/10000,"mm/dd/yyyy hh:nn:ss AM/PM") & "# And #" & Format([DischargedDate]+[DischargedTime]/10000+7,"mm/dd/yyyy hh:nn:ss AM/PM") & "#")) AS 7Day, Val(DCount("*","tblAdmissions","PatientID=" & [PatientID] & " And AdmittedDate+AdmittedTime/10000 Between #" & Format([DischargedDate]+[DischargedTime]/10000,"mm/dd/yyyy hh:nn:ss AM/PM") & "# And #" & Format([DischargedDate]+[DischargedTime]/10000+14,"mm/dd/yyyy hh:nn:ss AM/PM") & "#")) AS 14Day, Val(DCount("*","tblAdmissions","PatientID=" & [PatientID] & " And AdmittedDate+AdmittedTime/10000 Between #" & Format([DischargedDate]+[DischargedTime]/10000,"mm/dd/yyyy hh:nn:ss AM/PM") & "# And #" & Format([DischargedDate]+[DischargedTime]/10000+30,"mm/dd/yyyy hh:nn:ss AM/PM") & "#")) AS 30Day
FROM tblAdmissions
ORDER BY tblAdmissions.PatientID, tblAdmissions.AdmittedDate, tblAdmissions.AdmittedTime;

See attached version.

Readmissions2.zip

[scottb]

Looks like it is working. I am going to run a larger set of data through it tonight. Quick question: how does division by 10000 work with the discharged date+discharged time? I'm not clear on that divisibility unit. Thank you again very much for your help. This is terrific! - scott

[HansV]

Dividing by 10000 is a bit of a kludge.

Windows stores dates in the form of the number of days since December 30, 1899. For example, today (April 19, 2010) is 40,287.
Times are stored as fractions of a day, e.g. 6:00 AM is 0.25 and 12:00 PM is 0.5.
So April 19, 2010 6:00 AM should be 40,287.25.

The date columns in your table contain real date values, but the time columns don't contain real time values such as 10:43. They contain numbers such as 1043 to represent the time value 10:43.

I didn't want to take the trouble to convert the number 1043 to the actual fraction representing 10:43. I simply added 1043 / 10000 = .1043 to the date number. This will not be accurate, but that doesn't matter - it is sufficient to be able to compare date/time values.