D
Domenick
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'll also take suggestions on that method,
but I'd prefer to do it with straight query.
Thanks!
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'll also take suggestions on that method,
but I'd prefer to do it with straight query.
Thanks!