Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-28-2015, 02:16 PM   #1
eng3
Newly Registered User
 
Join Date: Jul 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
eng3 is on a distinguished road
Query Design View: Show results in two columns from one table

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 is offline   Reply With Quote
Old 07-28-2015, 02:55 PM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,317
Thanks: 540
Thanked 940 Times in 891 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Query Design View: Show results in two columns from one table

Can you use a combo box?
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-28-2015, 03:17 PM   #3
eng3
Newly Registered User
 
Join Date: Jul 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
eng3 is on a distinguished road
Re: Query Design View: Show results in two columns from one table

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 by eng3; 07-28-2015 at 03:50 PM.
eng3 is offline   Reply With Quote
Old 07-28-2015, 05:32 PM   #4
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,657
Thanks: 99
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Query Design View: Show results in two columns from one table

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.
Galaxiom is offline   Reply With Quote
Old 07-28-2015, 05:39 PM   #5
eng3
Newly Registered User
 
Join Date: Jul 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
eng3 is on a distinguished road
Re: Query Design View: Show results in two columns from one table

Quote:
Originally Posted by Galaxiom View Post
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

eng3 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DLoolup Show Results Values in Texbox on its own line multiple columns gcoaster Forms 7 04-12-2014 03:45 PM
table columns in record set ,how to view them in subform of a form in columns? hfs Modules & VBA 1 08-22-2013 12:19 PM
Results Show in Print Preview but not in the Report View amona Reports 1 07-08-2012 08:19 AM
the form view does not show the design view Foxlavolpe Forms 2 03-23-2008 05:21 AM
Fields in Design View don't show up in Form view. WesternActor Forms 2 08-04-2007 11:32 AM




All times are GMT -8. The time now is 08:34 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World