Update table based on closest match on date

[dalahans]

Hi

I have got 2 tables: "Orderlines" and "Pricedates"

Orderlines
Date of purchase Itemnr Price
2018-01-01 A 0
2018-01-02 A 0
2018-01-03 A 0
2018-01-04 A 0
2018-01-05 A 0
2018-01-06 A 0
2018-01-07 A 0
2018-01-08 A 0
2018-01-09 A 0
2018-01-10 A 0
2018-01-11 A 0
2018-01-12 A 0
2018-01-13 A 0
2018-01-14 A 0
2018-01-15 A 0
2016-01-15 B 0
2017-06-22 B 0
2018-01-05 B 0
2018-03-14 B 0



Pricedates
Item Price date Price
A 2018-01-02 2
A 2018-01-07 7
A 2018-01-11 11
A 2018-01-15 15
A 2018-01-22 22
A 2018-01-23 23
B 2016-01-01 1000
B 2017-01-01 2000
B 2018-01-01 3000
B 2018-02-02 4000
B 2018-03-03 5000


I need a query to update the field "Price" in the "Orderlines-table"
It should pick the price from the "Pricedate"-table at the valid date, itemnumber by itemnumber
The valid date is a date <= "Date of purchase"


This is what the result should look like:

Date of purchase Itemnr Price
2018-01-01 A
2018-01-02 A 2
2018-01-03 A 2
2018-01-04 A 2
2018-01-05 A 2
2018-01-06 A 2
2018-01-07 A 7
2018-01-08 A 7
2018-01-09 A 7
2018-01-10 A 7
2018-01-11 A 11
2018-01-12 A 11
2018-01-13 A 11
2018-01-14 A 11
2018-01-15 A 15
2016-01-15 B 1000
2017-06-22 B 2000
2018-01-05 B 3000
2018-03-14 B 5000


Any help is appreciated


// Hans

 

[dalahans]

I have posted this question in multiple forums:

http://www.accessforums.net/showthread.php?t=72372

https://www.access-programmers.co.uk/forums/showthread.php?p=1579833#post1579833

https://www.mrexcel.com/forum/microsoft-access/1058543-update-table-based-closest-match-date.html