Update table based on closest match on date (1 Viewer)

[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

I can solve this problem in Excel, but really need to do the same thing i Access. Any help is appreciated

I have posted this question in multiple forums:

accessforums.net/showthread.php?t=72372
access-programmers.co.uk/forums/showthread.php?p=1579833#post1579833
mrexcel.com/forum/microsoft-access/1058543-update-table-based-closest-match-date.html
(I tried to submitt the entire link, but got an error message stating that my post count must be 10 or greater to be able to submit links...)

// Hans

 

[isladogs]

Hans
You have already posted this at http://www.accessforums.net/showthread.php?t=72372 and been given detailed advice by Ajax and to a lesser extent by myself.

If you cross post, please follow forum etiquette rules. State you have done so and provide the link. See this article for further explanation https://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

 

[dalahans]

Hi Ridders.
I did not know about cross-posting, and tried to add the links, but was not able to
I got a message stating that I needed a post count > 10 to be able to post links...

 

[isladogs]

Having contributed to and followed the thread at one of the other forums, I'm unsure what you still need to learn how to do.

I think it would be fairer on other forum members to rewrite your question as you've already been given most of the answer at MAF. I haven't checked the other links.