Accomplish an Approximate Match Vlookup in Access (1 Viewer)

I am converting our invoicing out of Excel and into Access.

I have 2 tables:

1. Shipping (table has the ship date in ActualShipDate)
2. PPIescalation (has an EscalationFactor that is used to compute the unit price; this EscalationFactor changes yearly on 6/5 and the table holds that date in PriceChangeDate)

In Excel it was set up with Vlookup.

I need this done in a query: find the EscaltionFactor based on the ActualShipDate.

I tried a DLookup, I tried DMin too. I know my issue is that I want an approximate match.

PPIescalation Table
PriceChangeDate EscalationFactor
6/5/14 3.25
6/5/15 3.5
6/5/16 3.75

I need to have the following in my query

Invoice Query
ActualShipDate EscalationFactor
7/1/14 3.25
5/15/15 3.25
9/30/15 3.5
6/4/17 3.75

I tried googling which is where I got DLookup & DMin from.

Any thoughts would be helpful.

Thanks! Robecca

It appears that if you queried the PPIescalation Table for the PriceChangeDate dates before the ship date and they were sort in descending order by PriceChangeDate, the EscalationFactor you'd want would be in the first record in that query. You could probably use Dmin to get the date and then use that date in a DLookup to get the EscalationFactor or I think you could do it all at once in a subquery. Check out


http://www.allenbrowne.com/subquery-01.html#TopN

and

https://msdn.microsoft.com/en-us/library/office/aa217680(v=office.11).aspx

to get started with subqueries. Lots of fun.

Thank you! I appreciate the links.

I have never done a subquery or wrote in the SQL view. This is going to take me some time to comprehend what I need to do.

Robecca

Thank you for your help, your suggestion and links led me to learn that I could use a DMAX to get my EscalationFactor without a dlookup. Here is my field in the query:

Val(DMax("[56ID]","[56ProdAddCost]","DatePPI <= #" & [ActualShipDate] & "#"))

Sorry for the delay in responding!

I'm not sure how to mark this as solved.

Glad to hear you found a solution.

To mark a thread as solved, scroll to the top and click on Thread Tools in the lower menu bar. You should find a "Mark as Solved" option.

I really wish they'd put that in a better place.

Perfect, thanks Steve!