Hi
I have got 2 tables: "Orderlines" and "Pricedates"
Orderlines
Itemnr Date of purchase Price at date A 2018-01-01 0 A 2018-01-02 0 A 2018-01-03 0 A 2018-01-04 0 A 2018-01-05 0 A 2018-01-06 0 A 2018-01-07 0 A 2018-01-08 0 A 2018-01-09 0 A 2018-01-10 0 A 2018-01-11 0 A 2018-01-12 0 A 2018-01-13 0 A 2018-01-14 0 A 2018-01-15 0 B 2018-01-01 0 B 2017-06-22 0 B 2016-01-01 0 B 2018-03-14 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 at date" 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"
Any help is appreciated
// Hans