Solved

SQL Query to combine headers and values from two tables

Posted on 2007-04-03
8
227 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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