1

Lets say I have the follow Tables:

Outfits:
ID | Top Color ID | Bottom Color ID
1,2,1
2,3,4

Colors:
ID | Name
1, Red
2, Blue
3, Green
4, Orange

I'd like to have a query in design view to have the following result

Outfit ID | Top Color Name | Bottom Color Name
1, Blue, Red
2, Green, Orange

In design view, I can link "Color ID" to "Top Color ID" and "Bottom Color ID" but I don't know how to specify in the GUI to create "Top Color Name" and "Bottom Color Name"

1

Put this in the SQL View of the query design and you can then see the GUI required to build what you want.

select ID
, (select colorname from colors where id = outfits.topcolorid) as TopColorName
,(select colorname from colors where id = outfits.bottomcolorId) as BottomColorName
from outfits;
1
  • Thanks. Yes, I know I can do that, but I'd like to know how to do it in the GUI only without writing SQL. I'm trying to show a co-worker who has zero knowledge of SQL how to use access for a few simple tasks.
    – lmbgm
    Jul 28 '15 at 23:26
1

OK, I think I found a way. This may not be the best way (using the GUI) I create Query for each sub query.

On TopQuery: SELECT outfits.TopColorID, Colors.Color
FROM Colors INNER JOIN outfits ON Colors.ID = outfits.TopColorID;

On BottomQuery: SELECT outfits.BottomColorID, Colors.Color
FROM Colors INNER JOIN outfits ON Colors.ID = outfits.BottomColorID;

I can create these using the GUI easily.

Then on the main query, I add the above queries and link them. Then can select the color names from the appropriate query.

0

After further research, all I needed to do was drag the Colors table into the design view TWICE. Then link one to each ID (Top, Bottom) in the outfit table. Then set the alias and done.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.