Find date in Table 1 closest to another date in Table 2

[foxtrot123]

I have a table, tblVisits, holding patient's pre and post surgery visits:

PatientID VisitDate
1         1/5/12
1         3/10/12
1         9/1/13
2 ...

And another table holding patient's surgeries (each patient will have only one surgery)

PatientID  SurgeryDate
1          4/1/12
2 ...

I need to compare these two tables and create a variable that indicates which pre-surgery visit date (i.e., VisitDate < SurgeryDate) is closest to the surgery date. In the above example, it would return:

PatientID VisitDate  ClosestToSurgery
1         1/5/12     
1         3/10/12    Yes
1         9/1/13
2 ...

Any suggestions? I've tried various MIN and MAX approaches and can't seem to get it right.

 

[plog]

Queries like this you have to divide and conquer. Don't be afraid of using a bunch of subqueries to implement your logic--you can always go back and tighten them up later. You want to end up at Z, but for that you need to determine Y which is predicated on X which requires...etc.

For this you need 2 sub-queries. First, find how far away every visit is from the surgery date:

SELECT tblSurgeries.PatientId, tblVisits.VisitDate, DateDiff("d",[VisitDate],[SurgeryDate]) AS DaysBeforeSurgery
FROM tblSurgeries INNER JOIN tblVisits ON tblSurgeries.PatientId = tblVisits.PatientID
WHERE (((DateDiff("d",[VisitDate],[SurgeryDate]))>0));

Paste that into a query and name it 'sub_PreSurgeryVisits_1'. In the course of tightening up the query, I added criteria to exclude those occuring after the Surgery Date. Run it and you will see you get the first 2 results along with how far they are from the surgery.

Now use the MIN function to find the smallest difference in that set:

SELECT sub_PreSurgeryVisits_1.PatientId, Min(sub_PreSurgeryVisits_1.DaysBeforeSurgery) AS ClosestVisitToSurgery
FROM sub_PreSurgeryVisits_1
GROUP BY sub_PreSurgeryVisits_1.PatientId;

Paste that in and name it 'sub_PreSurgeryVisits_2'. With it you know the minimum difference between every surgery and pre-visit for every patient. To get the results you want, you have to link all that data together:

SELECT tblVisits.PatientID, tblVisits.VisitDate, IIf(IsNull([ClosestVisitToSurgery])=False,"Yes") AS ClosestToSurgery
FROM (tblVisits LEFT JOIN sub_PreSurgeryVisits_1 ON (tblVisits.VisitDate = sub_PreSurgeryVisits_1.VisitDate) AND (tblVisits.PatientID = sub_PreSurgeryVisits_1.PatientId)) LEFT JOIN sub_PreSurgeryVisits_2 ON (sub_PreSurgeryVisits_1.PatientId = sub_PreSurgeryVisits_2.PatientId) AND (sub_PreSurgeryVisits_1.DaysBeforeSurgery = sub_PreSurgeryVisits_2.ClosestVisitToSurgery);

Open all those queries up in design view to understand what I did, if you have any questions let me know.

 

[foxtrot123]

Plog - That worked perfect. Thanks much!