Solved

SQL Query to combine headers and values from two tables

Posted on 2007-04-03
8
228 Views
Last Modified: 2008-03-19
Hello All,

I am trying to write an sql query via query analyzer in excel, or ms access that will combine the fields from one table as the header information and the values from another table as the row information.
For example....Talble1 has headeritem1, headeritem2, headeritem3,.......and table2 has value1, value2, value3...
I want to get a grid of values like
header1,header2,header3
value1,value2,value3

I am hoping someone can help me sort this out
Thanks for the help
0
Comment
Question by:pattersonr
8 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18846910
select field1, field2, field3 from yourtable
union
select field1, field2, field3 from yourtable;

the union query will do the job
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 18847035
pattersonr,
first you said
<that will combine the fields from one table as the header information and the values from another table as the row information.>

then

I want to get a grid of values like
header1,header2,header3
value1,value2,value3

can you make this  clearer?

0
 
LVL 1

Author Comment

by:pattersonr
ID: 18847267
I hope so...
I have two linked tables in an sql database.....
TABLE1 has the following
break1, break2, break3
With values like 3+,10+,25+
TABLE2 has the following
value1,value2,value3
With values like
121.75,131,25,111,12
222.32,234.56,232,12



When the query is finished I woudlike to see

3+,10+,25+
121.75,131,25,111,12
222.32,234.56,232,12

With the , being field seperators...

I hope that helps alittle...
I think the union query mentioned above might be the right track...I just havent got it to work yet...
Will take any and all solutions....:)
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 34

Expert Comment

by:jefftwilley
ID: 18847994
The union would work...but not to produce the commas..these values would simply appear in the same columns as your heaings. the key to the union query is that each table brought in must have the same number of fields. if you want groupings of Table2 divided by commas, to appear under specific heaidngs, then we need more information.

Field1     field2   field3    field4   field5
3+          10+     25+      ?           ?
121.75   131      25        111      12
0
 
LVL 1

Author Comment

by:pattersonr
ID: 18854961
Missed your response...sorry for the delay...
My apologies....some of the commas in the above post should have been periods...So you are correct that the uniion query would work as you mention...
I guess let me explain the end result of what I want to achieve....I am wanted to make a pivot table report in something like excel...That would allow the end user to select a specific column or multiple columns.  Each of these headers is also associated with a specific company so that would allow the user to select specific companies as well.  The basic problem as I see it so far is that the header information and the subvalues are stored in the two different tables.  I must combine them and somehow get excel/or access to see the first datarow as the header not as an additional data row.  Excel may be able to handle this...I just havent figured it out yet.

Let me konw if that helps clarify anything or gives you some additional thoughts.  I am a bit stuck on what to do after the uniion of the two datafields to acheive the desired behavior.

Thanks again
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 18855027
you can use the transferspreadsheet command with your union query to get your data into the spreadsheet, and there is an option to tell the export that the first row contains column headings.

You can get to this through your macro builder.

So as long as your header table produces only 1 row, you're golden
J
0
 
LVL 1

Expert Comment

by:Computer101
ID: 21160007
Forced accept.

Computer101
EE Admin
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now