Ms-access – Access Query Design View: Show results in two columns from one table

ms access

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"

Best Answer

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;

Related Solutions

Ms-access – Microsoft Access constrain view by date in outer query

How about:

SELECT a.orderid,
       a.ocustomerid
FROM   orders AS a
       INNER JOIN (SELECT b.orderid,
                          b.ocustomerid
                          b.opromisedshipdate
                   FROM   orders AS b
                   ) AS c
       ON c.orderid = a.orderid 
WHERE  c.opromisedshipdate = a.opromisedshipdate

Ms-access – Merging two Access tables into one

Create a new table that have all the columne in all your 5 tables, then create the needed SQL statment like SELECT * FROM Tb1 INTO TableWithAllColumns eventually add some WHERE to exclude the duplicate rows from the insert.

This will work if your table doesent have similar rows. If you need to merge similar rows together, there is no a simple way to tho this. The best you can do is find, or write, a tool that can compare all the value in all the columns and act in the right way.

Best Answer

Related Solutions

Ms-access – Microsoft Access constrain view by date in outer query

Ms-access – Merging two Access tables into one

Related Question