Query Design View: Show results in two columns from one table (1 Viewer)

eng3

New member
Local time
Today, 05:11
Joined
Jul 28, 2015
Messages
3
Lets say I have the follow Tables:

Code:
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

Code:
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"
 

eng3

New member
Local time
Today, 05:11
Joined
Jul 28, 2015
Messages
3
No, I need the output to be like I showed in the first post

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

Code:
    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.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:11
Joined
Jan 20, 2009
Messages
12,851
Combos could show it as required with a simple query. The combo RowSource needs to be the Colors table and the ColumnWidths and ColumnCOunt adjusted to show the Color.

Otherwise it can be done in a single query like this:

Code:
SELECT Outfits.outfitID, ColorT.Color AS TopColorName, ColorB.Color AS BottonColorName
FROM (
Outfits 
INNER JOIN Colors AS ColorT
ON Outfits.TopColorID=ColorT.Color
)
INNER JOIN Colors AS ColorB
ON Outfits.TopColorID=ColorB.Color
;
The aliases for Color table can be generated by dragging the table into the designer twice. It will give them different names from what I used.
 

eng3

New member
Local time
Today, 05:11
Joined
Jul 28, 2015
Messages
3
The aliases for Color table can be generated by dragging the table into the designer twice. It will give them different names from what I used.

Ah, just drag it in twice. I see. Thanks
 

Users who are viewing this thread

Top Bottom