Access 2007 Query - multiple lookup criteria


I would like a query that takes the two fields from Color Pairs and adds another field that shows the corresponding cost of that pair of colors as given in the Costs for Color Pairs table. Could you help me with this using the design view? I would rather not get into SQL just yet...

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helen FeddemaCommented:
There is a lot of redundancy in these tables.  I think the lookup table is all you need, as it includes the ID, the color pair and the cost for each ID.  You can then use the ID in other tables as a foreign key to retrieve the color pair or the cost.  
JC_LivesAuthor Commented:
Thanks for your quick response! Well, I'm looking for a query that generates the following:

Red     Green    4
Green   Blue     3
Red     Green   4
Green  Green   1

It takes the table "Color Pairs" and grabs the correct cost based on the pair listed.

I'm not worried about redundancy... I'm just looking to understand how to generate a query that looks up data and populates a field automatically. Please do not come up with an alternate solution... I need to understand how to use a query in design view to produce this output.

Chuck WoodCommented:
I think what you want is what the qryColorMatches query in the attached database provides.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JC_LivesAuthor Commented:
That's it... thanks so much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.