Lookup one table to another.

[Guest]

I have two tables; the first table is the one I want to gather information
for, pulling from the second table.

I want to lookup in table 2 the closest match without going over and gather
the cost.

Examples
PCN CD15 w APM 1400 would equal 0.02376
PCN CD15 w APM 6000 would equal 0.025512
PCN CD20 w APM 6000 would equal 0.019239

Is this possible using a DLookup or DMin or DMax in an Access Query?

Table One

PCN APM
CD15 1400
CD15 1500
CD15 6000
CD15 1200
CD20 1500
CD20 6000


Table Two

Model Vol ServCost
CD15 1000 0.02376
CD15 3000 0.02266
CD15 5000 0.025512
CD15 8000 0.0209231
CD15 10000 0.0203269
CD15 12500 0.0197615
CD15 15000 0.0192258
CD15 17500 0.0183095
CD15 20000 0.017545
CM20 1000 0.02327919
CM20 3000 0.0211629
CM20 5000 0.019239
CM20 8000 0.01749
CM20 10000 0.0159
CM20 12500 0.0159
CM20 15000 0.0159
CM20 17500 0.0159
CM20 20000 0.0159

 

[Michel Walsh]

If the ServCost value is a sequence of value never increasing, you can use a
DMin:

DMin( "ServCost", "Table2", "Model=""" & PCN & """ AND VOL <= " & APM )



Indeed, for PCN= CD15 and APM =6000, the third argument evaluate to:

Model="CD15" AND VOL <=6000


The Domain function keeps the three records:

CD15 1000 0.02376
CD15 3000 0.02266
CD15 5000 0.025512



and the MINimum value: 0.025512


Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

well, seems the minimum value WON'T be 0,025512. Note it is more
'economical' to use a volume of 3000 + two volume of 1000 than a single
volume of 5000 !

The problem occurs because for a vol or 5000, the value increased, rather
than decreased. If this is a typo, the proposed solution should work. If it
is not a typo, the required query is more complex than a single Domain
function.


Vanderghast, Access MVP