Using a Table as Lookup

[Greg]

I have a table (A) received each month with a number value in it, for
example "Units Purchased". I have another table (B) that changes quarterly
or semi annually with a value of "Units Purchased" .

For example table (B):
Units Purchased, Discount, Last Change
100 .05 1/1/2009
200 1.0 1/1/2009
310 1.5 1/1/2008
475 2.0 3/1/2008


I would like to use the value in the field "Units Purchased" in table A. for
a lookup in table B.

For Example, if table A has a value of 175, I would like to look this value
up in table B and get the record with the "Units Purchased" value of 200,
1.0, 1/1/2009. I will the use the returned value in the query to perform
some calculations.

Can anyone point me in the direction of solving this situation in ACCESS.
For the sake of explanation, it would be similar to the functioning of the
EXCEL VLOOKUP function.

 

[Michel Walsh]

SELECT a.unitPurchase, b.UnitPurchase, b.discount, b.LastChange
FROM (tableA AS a INNER JOIN tableB AS b
ON a.unitPurchase <= b.UnitPurchase)
WHERE b.UnitPurchase=(SELECT MIN(c.UnitPurchase)
FROM tableB AS c
WHERE a.unitPurchase <= c.unitPurchase)


or, using joins rather than sub-query:


SELECT a.unitPurchase, b.UnitPurchase, b.discount, b.LastChange
FROM (tableA AS a INNER JOIN tableB AS b
ON a.unitPurchase <= b.UnitPurchase)
INNER JOIN tableB AS c
ON a.unitPurchase <= c.UnitPurchase
GROUP BY a.unitPurchase, b.UnitPurchase, b.discount, b.LastChange
HAVING b.UnitPurchase = MIN(c.UnitPurchase)





Vanderghast, Access MVP

 

[Greg]

Thank You very much I have gotten both of the suggestions to work.!
However I am having a difficult time grasping the subquery concept.
My background as old as it is basically "procedural". I have gotten the
query you suggested to work but I can't honestly say why.
Does anyone have any suggestions as to getting an understanding of sub
queries??
Thanks in advance,
Greg

 

[Michel Walsh]

My fault, I 'inverted' the comparisons (and min/max) :



WHERE b.UnitPurchase=(SELECT MAX(c.UnitPurchase)
FROM tableB AS c
WHERE a.unitPurchase >= c.unitPurchase)



which, basically, take all the records with a unitPurchase less or equal to
the actual one (the actual one is a.unitPurchase and the ones from the
table of lookups are c.unitPurchase), keeping the biggest one, MAX, to
determine the applicable discount.




Vanderghast, Access MVP