Closest Match Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to identify for each record in Table1, the closest match in Table2 for
PartNmber, based on date and retreive the price. I only want to return
one-the closest. My end result will allow me to determine the 'price' for
each item in Table1 from the closest match in Table2. How can I accomplish
this?

TABLE 1
BillDocum PartNmber BillingDate
90255065 12742552 3/30/2006 <------
90255065 12742553 3/30/2006 <--------
90255065 12742941 3/30/2006
90255930 11055610 4/7/2006
90255930 11110468 4/7/2006
90255930 11121366 4/7/2006
90255930 11164488 4/7/2006
90255931 12742552 11/16/2006
90255932 12742553 6/1/2006

TABLE 2
InvDocum PartNmber Date Price
100000194 12742552 2/1/2006 50
100000195 12742552 2/15/2006 51
100000196 12742552 2/27/2006 52 <------------- want to select this one
100000199 12742552 11/14/200655
100000200 12742553 2/15/2006 100
100000201 12742553 2/16/2006 110
100000202 12742553 4/1/2006 115 <---------- want to select this one
100000203 12742553 4/15/2006 100
100000204 12742553 4/30/2006 101

DESIRED END RESULT
BillDcment PartNmber BillingDate Price
90255065 12742552 3/30/2006 52 <------
90255065 12742553 3/30/2006 115 <-----
90255065 12742941 3/30/2006 etc
90255930 11055610 4/7/2006 etc
90255930 11110468 4/7/2006 etc
90255930 11121366 4/7/2006 etc
90255930 11164488 4/7/2006 etc
90255931 12742552 1/16/2006 etc
90255932 12742553 6/1/2006 etc
 
You can use the min(abs(datediff("d",[field1],[field2])))

But I see what I think is an error in your thinking. Using the information
below --
90255065 12742553 3/30/2006
100000201 12742553 2/16/2006 110
100000202 12742553 4/1/2006 115

The price was 110 on 3/30/2006, not 115 as you indicated. The price had not
changed untill a day later.

So I think you need to base price on field1 <= field2 or use price date of
same or less.
 
Back
Top