• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

Access 2007 Query - multiple lookup criteria

Hi,

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...

Thanks!!!!
Multiple-lookup-criteria---quest.accdb
0
JC_Lives
Asked:
JC_Lives
  • 2
1 Solution
 
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.  
0
 
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.

Thanks!!
0
 
Chuck WoodCommented:
I think what you want is what the qryColorMatches query in the attached database provides.
--Chuck
Multiple-lookup-criteria---quest.accdb
0
 
JC_LivesAuthor Commented:
That's it... thanks so much!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now