Calculated fields - date difference

[HGCanada]

I have a database with a parent table for basic patient information, and a tPatientStatusDates table (data entry via continuous sub-form) which logs, for each patient, the date that they reach each of 6 stati. I'd like to be able to report the wait times between each status type. For example, how many days between status 1 (referral) and status 2 (triage), and other wait times.

I think the way to do this is to add calculated fields to my tPatientStatusDates table, and calculate the differences between the various stati. Is this the right way to do it? Can someone please tell me how to build this expression?

My tPatientStatusDates table has the following fields:

- PatientID (unique auto-number ID for each patient)
- PtStatusID (autonumber - unique ID for each time a new status is logged for any patient)
- StatusID (records the status as a number 1-6 - related to a lookup table where 1 = referral received, 2 = triage completed, etc ... up to 6 = discharged from care)
- StatusDate

And I just added the following calculated fields to this table, but I don't know how to program the calculations.
- ReferralToTriageWait (calculate # of days between status 1 and status 2)
- TriageToFirstVisitWait (calculate # of days between stati 2 and 3)
- FirstVisitToStreamingWait (calculate # of days between stati 3 and 4)
- StreamingToFollowUp (calculate # of days between stati 4 and 5)
- FollowUpToDischarge (calculate # of days between stati 5 and 6)

I'm only a novice-intermediate user. I don't have experience with this. Any help would be greatly appreciated. Thanks in advance!

 

[JHB]

Could you post some sample data in an Excel sheet or MS-Access database.

 

[Minty]

I don't think you can use a calculated table field for this as it will involve a sub query.
To make this work efficiently, I think you will need to make a table with the Previous status ID and the current ID e.g.

1 , 2
2 , 3
3 , 4
etc.

You can then use a query to look up the dates and hence calculate the difference. Using a table will scale better if you ever need to add a status "into" you current list, as you'll only need to adjust the table data not the code / queries behind it.

 

[HGCanada]

Thank you. I think I need to wrap my mind around this a bit more.

JHB - I've attached some sample data.

 

[arnelgp]

there are broken StatusID, so it
returned "No Info" from the function.
look at query1 and module1.

 

[HGCanada]

Thank you arnelgp, and everyone! The reason it didn't work was because I simplified when I described the numbering of the stati. Actually, the numbers in my database are not 1-6. They are 1, 3, 4, 6, 8, 9. It's a long story why they are not sequential, but I left it that way on purpose for the time being. Your code worked once I re-numbered. Thank you!

I tried something else though:

- created 6 separate queries- 1 for each status. Each query creates a table with the date each patient reached that status
- created a second query, pulling all the 6 queries together, and introducing datediff functions for the required wait times calculations.

This seems to work well for my purposes. I included some patient demographics data. I think this will give the users all they need. They can just export the data, and basically come up with a table indicating all the patients, the dates they reached each status, and wait times for each sequential status.