Concurrent/consecutive events

[pekara2]

Hi

I'm hoping that someone can help me with this issue before I become folically challenged..... I work in Health and one the areas that I want to investigate is the transfer of patients between hospital sites. So far no problem as long as the patient only moves a limited number of times biut its not proving to be that simple......

ID	Patient	Admission date	Discharge date	site
1	Mrs White	1/1/15	7/1/15	a
2	Mr Pink	3/1/5	6/1/15	a
3	Mr Pink	6/1/15	10/1/15	b
4	Mrs White	7/1/15	12/1/15	b
5	Ms Black	8/1/15	12/1/15	a
6	Mrs White	8/1/15	8/1/15	a
7	Mr Pink	10/1/15	12/1/15	c
8	Ms Black	12/1/15	18/1/15	b
9	Mr Pink	12/1/15	15/1/15	a
10	Mr Pink	15/1/15	28/1/15	b

<TBODY>
</TBODY>

What I need to get to is - can anyone help?

ID	Patient	Admission date	Discharge date
A1	Mrs White	1/1/15	12/1/15
A2	Mr Pink	3/1/15	28/1/15
A3	Ms Black	8/1/15	18/1/15

<TBODY>
</TBODY>

 

[pgc01]

Hi

Can you not just take for each patient the minimum date for Admission and the Maximum for Descharge?

 

[pekara2]

Not really as the patient might be admitted again for another (unlinked) spell e.g. Mrs White might be an inpatient again on the 17/4/15...... It is driving me to distraction at the moment....

 

[Comfy]

I've added an additional 4 columns to the Table

Text - InSite
Text - OutSite
DateTime - InDate
DateTime - OutDate

This piece of code would then update all the fields looking for continued stay.

Option Compare Database
Sub ContinuedStay()
Dim rs As DAO.Recordset
Dim tempRs As DAO.Recordset
Dim pRecord As Variant
Dim temp As Variant


Set rs = CurrentDb.OpenRecordset("SELECT * From Data")
temp = rs.GetRows(10)
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True
        Set tempRs = CurrentDb.OpenRecordset("SELECT * From Data WHERE Patient = '" & rs!Patient & "' AND [Admission date] = #" & Format(rs("Discharge date"), "mm/dd/yyyy") & "#")
        If tempRs.RecordCount > 0 Then
            pRecord = tempRs.GetRows(1)
            Do Until tempRs.RecordCount = 0
                Set tempRs = CurrentDb.OpenRecordset("SELECT * From Data WHERE Patient = '" & pRecord(1, 0) & "' AND [Admission date] = #" & Format(pRecord(3, 0), "mm/dd/yyyy") & "# AND [Discharge date] <> #" & Format(pRecord(3, 0), "mm/dd/yyyy") & "#")
                If tempRs.RecordCount > 0 Then pRecord = tempRs.GetRows(1)
            Loop
            tempRs.Close
                rs.Edit
                rs!InSite = rs!Site
                rs!OutSite = pRecord(4, 0)
                rs!InDate = rs("Admission date")
                rs!OutDate = pRecord(3, 0)
                rs.Update
        Debug.Print rs!ID
        Else
                rs.Edit
                rs!InSite = rs!Site
                rs!OutSite = rs!Site
                rs!InDate = rs("Admission date")
                rs!OutDate = rs("Discharge date")
                rs.Update
                Debug.Print rs!ID
        End If
        rs.MoveNext
        Erase pRecord
    Loop
Else
    MsgBox "There are no records in the recordset."
End If


MsgBox "Finished looping through records."


rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
Set tempRs = Nothing 'Clean up
End Sub

You could then write a query to return the min(InDate) and Max(OutDate) for each patient???

Excel 2010

	A	B	C	D	E	F	G	H	I
1	ID	Patient	Admission date	Discharge date	site	InSite	OutSite	InDate	OutDate
2	1	Mr Pink	03/01/2005	06/01/2015	a	a	a	03/01/2005	28/01/2015
3	6	Mrs White	01/01/2015	07/01/2015	a	a	c	01/01/2015	12/01/2015
4	8	Mrs White	08/01/2015	08/01/2015	b	b	b	08/01/2015	08/01/2015
5	9	Ms Black	08/01/2015	12/01/2015	a	a	b	08/01/2015	18/01/2015

<tbody>
</tbody>

Data​

A possible erroneous entry at ID 8 as Mrs White was at two different Hospitals on the same date.

 

[Comfy]

Here's what the Query might look like.

Select A.*, B.InSite, B.OutSite FROM
(SELECT DISTINCT Patient, Min(InDate) As Admission, OutDate As Discharge FROM Data
Group By Patient, OutDate) As A
LEFT Join Data As B
ON A.Patient + Format(A.Admission, "ddmmyy") = B.Patient + Format(B.InDate, "ddmmyy")