DataBase - Join based on next closest value (like Excel VLOOKUP)

Asked By Domenick

21-Nov-09 10:52 AM

Trying to do something similar to a VLOOKUP (Excel) in an Access 2003 query.
I have the following tables:

JOBS
Job,Quantity
A,96
B,256
C,300
D,4299

COSTS
Quantity,Cost
0, $1000
100, $1200
200, $1500
300, $2000
400, $2500
500, $3000

I need a query that takes JOBS.Quantity, looks it up in COSTS.Quantity and
find the cost for the NEXT LOWEST quantity.  (Example: Job B has a quantity
of 256 and the next LOWEST quantity from COSTS is 200 so Job B costs $1500.)

The results should be as follows:

JOBS.Job,COSTS.Cost
A,$1000
B,$1500
C,$2000
D,$3000

This would be easy to do in Excel with VLOOKUP. I'd like to do this with a
SQL query. I believe it can be done with non-equijoins and/or some grouping
with MAX or MIN. Can anyone offer a suggestion? I saw some stuff on the web
about using custom functions so I will also take suggestions on that method,
but I'd prefer to do it with straight query.

Thanks!

21-Nov-09 12:04 PM

Assuming that cost increases with increased quantity:

SELECT Job, MAX(Cost)
FROM Jobs INNER JOIN Costs
ON Jobs.Quantity >= Costs.Quantity
GROUP BY Job;

Ken Sheridan
Stafford, England


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200911/1