Join two tables by joining the same date or the nearest date (not just exact matches)

Question

I have two tables:

• CustomerID
• Lead_date
• Lead_source

and

• CustomerID
• Product_Interest_Date
• Product_Interest

I want the two to create a single table where, for each CustomerID, each Product_Interest connects to Lead_Source, which is the closest date (but not after). The resulting table will be:

• CustomerID
• Product_Interest_Date
• Product_Interest
• Lead_Date (nearest record at Product_Interest_Date)
• Lead_Source (Lead_Source nearest Lead_Date)

So far I can join the tables and create a new field that calculates the nearest date without going over, but when I try to use a group using Min, I still get a few permutations (each Lead_Date for each Product_Interest). Here is the code:

SELECT Min(Int(Abs([Test_PI]![Product_Interest_Date]-[Test_Leads]![Lead_Date]))) AS Lead_PI_Link, Test_Leads.CustomerID, Test_PI.Product_Interest_Date, Test_PI.Product_Interest, Test_Leads.Lead_Date, Test_Leads.Lead_Source FROM Test_Leads INNER JOIN Test_PI ON Test_Leads.CustomerID = Test_PI.CustomerID GROUP BY Test_Leads.CustomerID, Test_PI.Product_Interest_Date, Test_PI.Product_Interest, Test_Leads.Lead_Date, Test_Leads.Lead_Source HAVING (((Test_Leads.CustomerID)="C6UJ9A002Q2P")); 

This CustomerID has 4 entries in Test_Leads and 4 entries in Product_Interest. The result of this query gives 16 results instead of the desired 4. If the dates were an exact match, I could add a criterion that the date difference was "0", but sometimes these dates are compensated for 1 day, sometimes many days.

I use Access, and would prefer a "native" solution, but for all this for something!

Answer 1

Test_PI

 CustomerID Product_Interest_Date Product_Interest ---------- --------------------- ---------------- 1 2014-09-07 Interest1 1 2014-09-08 Interest2 1 2014-09-15 Interest3 1 2014-09-28 Interest4 

Test_Leads

 CustomerID Lead_Date Lead_Source ---------- ---------- ----------- 1 2014-09-07 Source1 1 2014-09-14 Source2 2 2014-09-15 Source3 1 2014-09-21 Source4 

The trick here is to use an unequal join as part of a subquery to identify the most recent Lead_Date for each Product_Interest_Date. Inquiry

 SELECT pi.CustomerID, pi.Product_Interest_Date, l.Lead_Date FROM Test_PI pi INNER JOIN Test_Leads l ON pi.CustomerID = l.CustomerID AND pi.Product_Interest_Date >= l.Lead_Date 

returns

 CustomerID Product_Interest_Date Lead_Date ---------- --------------------- ---------- 1 2014-09-07 2014-09-07 1 2014-09-08 2014-09-07 1 2014-09-15 2014-09-07 1 2014-09-15 2014-09-14 1 2014-09-28 2014-09-07 1 2014-09-28 2014-09-14 1 2014-09-28 2014-09-21 

Notice how two matches are returned for 09-15, and three matches are returned for 09-28. We are only interested in the latter, so we will select this request a little

 SELECT pi.CustomerID, pi.Product_Interest_Date, Max(l.Lead_Date) AS MaxOfLead_Date FROM Test_PI pi INNER JOIN Test_Leads l ON pi.CustomerID = l.CustomerID AND pi.Product_Interest_Date >= l.Lead_Date GROUP BY pi.CustomerID, pi.Product_Interest_Date 

which returns

 CustomerID Product_Interest_Date MaxOfLead_Date ---------- --------------------- -------------- 1 2014-09-07 2014-09-07 1 2014-09-08 2014-09-07 1 2014-09-15 2014-09-14 1 2014-09-28 2014-09-21 

Now we can join the two tables along with this query to put it all together

 SELECT Test_PI.CustomerID, Test_PI.Product_Interest_Date, Test_PI.Product_Interest, Test_Leads.Lead_Date, Test_Leads.Lead_Source FROM ( Test_PI INNER JOIN ( SELECT pi.CustomerID, pi.Product_Interest_Date, Max(l.Lead_Date) AS MaxOfLead_Date FROM Test_PI pi INNER JOIN Test_Leads l ON pi.CustomerID = l.CustomerID AND pi.Product_Interest_Date >= l.Lead_Date GROUP BY pi.CustomerID, pi.Product_Interest_Date ) latest ON Test_PI.CustomerID = latest.CustomerID AND Test_PI.Product_Interest_Date = latest.Product_Interest_Date ) INNER JOIN Test_Leads ON Test_Leads.CustomerID = latest.CustomerID AND Test_Leads.Lead_Date = latest.MaxOfLead_Date 

return

 CustomerID Product_Interest_Date Product_Interest Lead_Date Lead_Source ---------- --------------------- ---------------- ---------- ----------- 1 2014-09-07 Interest1 2014-09-07 Source1 1 2014-09-08 Interest2 2014-09-07 Source1 1 2014-09-15 Interest3 2014-09-14 Source2 1 2014-09-28 Interest4 2014-09-21 Source4