Associate records based on date values

[foxtrot123]

I have data like so. This is from a junction table that creates a many-to-many between tblPatients and tblEvents. PtEventID is the PK, PtID is a FK to tblPatients, and EventID is a FK to tblEvents.

(I'm showing Event to make it easier to understand.)

PtEventID PtID EventID  Event       EventDate
1         1       1     Office      1/1/08
2         1       1     Office      1/5/08
3         1       2     Surgery     2/1/08
4         1       3     ComplicABC  2/2/08
5         1       4     ComplicXYZ  2/8/08
6         1       2     Surgery     3/10/08
7         1       3     ComplicABC  3/10/08

etc.

The above patient had two surgeries. After the first surgery - but before his second surgery - he had two complications. On the day of his second surgery, he had one complication.

I need to use the EventDate to associate the complications to the surgical event that came immediately before it (or occurred on the same day). This is obviously better done ahead of time with a different table structure, but this is what I'm working with.

The query should return something like this:

PtID EventID Event     EventDate  Complication ComplicationDate
1    1       Office      1/1/08          
1    1       Office      1/5/08
1    2       Surgery     2/1/08     ComplicABC   2/2/08
1    2       Surgery     2/1/08     ComplicXYZ   2/8/08
1    2       Surgery     3/10/08    ComplicABC   3/10/08
etc.

Any sugggestions for going about this? Thank you.

 

[plog]

Here's a solution using 2 sub-queries. You didn't give the name of your table, so for all instances below of 'YourTableNameHere', replace it with the name of your table. Also, I defined a complication as any event that started with 'Complic' and a Surgery as an Event that equals 'Surgery'. If either of those are untrue, then this will fail.

Use this SQL for the first sub query:

SELECT YourTableNameHere.Event AS Complication, YourTableNameHere.EventDate AS ComplicationDate, YourTableNameHere.PtID, CDate(DMax("[EventDate]","YourTableNameHere","[PTID]= " & [PTID] & " AND [Event]='Surgery' AND [EventDate]<=#" & [EventDate] & "#")) AS PriorSurgery
FROM YourTableNameHere
WHERE (((YourTableNameHere.Event) Like "Complic*"));

Name it 'subComplications'. It identifies all the complications and determines the date of the patients last surgery up to and including the complication date. This is the SQL of the second sub-query:

SELECT YourTableNameHere.PtEventID, subComplications.Complication, subComplications.ComplicationDate
FROM YourTableNameHere INNER JOIN subComplications ON (YourTableNameHere.PtID = subComplications.PtID) AND (YourTableNameHere.EventDate = subComplications.PriorSurgery)
WHERE YourTableNameHere.Event="Surgery";

Name it 'subSurgeryComplications'. It takes the PtID and Last Surgery date determined in subComplications and gets the actual PtEventID of the surgery to make linking to that surgery exact. Below is the final query which will produce your desired results:

SELECT YourTableNameHere.Event, YourTableNameHere.PtID, YourTableNameHere.EventID, YourTableNameHere.Event, YourTableNameHere.EventDate, subSurgeryComplications.Complication, subSurgeryComplications.ComplicationDate
FROM YourTableNameHere LEFT JOIN subSurgeryComplications ON YourTableNameHere.PtEventID = subSurgeryComplications.PtEventID
WHERE (((YourTableNameHere.Event) Not Like "Complic*"));

 

[foxtrot123]

Outstanding! That worked great. The last query still returned the original records for the complications, so I tweaked it slightly to remove those.

Much thanks. I just posted a similar question with similar data here:
http://www.access-programmers.co.uk/forums/showthread.php?p=1228757#post1228757

Slightly different beast to tackle, which I think will need a crosstab of sorts.