Access Query Design View: Show results in two columns from one table

Question

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"

Answer 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;

Answer 2

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.

Answer 3

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.