Approximate Match

[JustABeginner]

Hi,

I know that in Excel it is east to return in Approximate Match using Vlookup, but I am struggling to replicate that in Access.

I wish to summarise two tables that show the contract month, and the contract price (which changes quarterly):

The first table is called TblContractMth
ID (Primary Key) MonthStart
1 01/01/18
2 01/02/18
3 01/03/18
4 01/04/18
5 01/05/18

The second table is called TblContractCost
Effective From Date Price
01/01/18 £4.50
01/04/18 £5.25


I need a query (or table) that displays the applicable Price next to the applicable 'MonthStart'.

I've looked at DMax, DLookup, Iif but cannot crack it. All help gratefully received.

Andy

 

[isladogs]

One way is to create 2 queries adding a field to determine the quarter for each date

DatePart("q",[MonthStart])

If your results will go into more than one year then use

DatePart("q",[MonthStart]) & "-" & DatePart("y",[MonthStart])

Similarly for the 2nd table

Now make a 3rd query joining the 2 quarter fields and add the fields MonthStart & Price

 

[JustABeginner]

Thanks Ridders,

What would happen if the MonthStart was not conveniently at the start of a quarter? I'm guessing the solution posted below would only work as long as it does?

 

[arnelgp]

You can use:

Selec id, MonthStart, (select top 1 price from TblContractCost where [Effective From Date] <= TblContractMth.MonthStart Order by [Effective From Date] Desc) As [Applicable Price] From TblContractMth;

Jan, feb, mar will have same price.
Since the next price change will take effect on mar

 

[isladogs]

Thanks Ridders,

What would happen if the MonthStart was not conveniently at the start of a quarter? I'm guessing the solution posted below would only work as long as it does?

if that were the case, I would use a look up table with two fields: MonthNo and Qtr or if quarters started mid month, additional column for day no
I would use that in a query to calculate the quarter for any specific date.

You might find this thread interesting where quarters are non-standard e.g. Dec, Jan, Feb in one quarter
http://www.accessforums.net/showthread.php?t=71189

 

[Mark_]

You CAN get DLookup to work like VLookup, you just need to understand how DLookup works.

If you make a query on The TblContractMth that is ordered (date, descending) you can use DLookup against the query using a criteria of "[TblDate] <= #" & Me.YourDateHere & "#". DLookup would NORMALLY go in record order and return the first record matching. As you will be using a query that is in descending order the first match in record order will also be the highest value.

Not a great way to do it but it can be done.