How to perform a LOOKUP when dependent on 2 values?

[David Jacobs]

I have a database of paint colors that I purchase. Each color has 3 purchase costs depending on the size of container I buy. For Example, my code for containers is K3, DM, and TT. An example price for Green is $5.97, $5.28, and $4.85 (for K3, DM, TT purchases). For Blue, I have $5.86, $5.23, and $4.75. I have a master table of colors (rows) and prices (columns). What I want is when my purchasing agent enters the color (from a dropdown list) and the container type (K3, DM or TT), I want a field to return me the correct purchase price. I am confused on how to define the unit price. I know I need to tell it to look up the color and then look in the proper container-type column, but it seems I need to have a double look-up function. Can I inbed a lookup within another lookup function?

[David Jondreau]

You can't do a look up based on two values...but you can combine two values into one.

Create a text calc field in each of your two tables (Invoice and Prices) named PriceLookup = Size & " " & Color.

Relatet the two tables by PriceLookup and you'll have the structure you need to build your lookup.

BTW, this is the wrong subcategory for this question.

[Genx]

... Why can't you do a lookup based on two values?

All you need to do is add an extra criteria to the relationship

I.e. Color = Color

Size = Size

And then lookup over that relationship

[Ender]

Unforunately, FM6 and below require the concatonation technique that grip described in order to achieve a multi-criteria relationship.

[Genx]

God i just never look at the version do I?

My appologies