[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Union Query

Posted on 2014-04-24
6
Medium Priority
?
292 Views
Last Modified: 2014-04-25
I am very unfamiliar with union queries... I have 5 tables that I would like to show the results of on one query. They all share one common column that is "DateSpec" there are only 4 different values that that could be. The ending table would be a 4 rowed table of the 4 datespec values. Then the columns would be all the different columns of the 5 tables.

DateSpec            Column1                           Column2
Value1                 ValuefromTable1              ValuefromTable2
Value2                 ValuefromTable1              ValuefromTable2
Vlaue3                 ValuefromTable1              ValuefromTable2
Vlaue4                 ValuefromTable1              ValuefromTable2

There would be about 15 tables in the end. Any idea? I think it is simple... I just am very unfamiliar with union queries. THanks!
0
Comment
Question by:cansevin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 40021862
Union queries can be thought of as stacking the individual query results on top of one another.  They need to have the same number of columns and each column's data types in each contributing query should to be the same.

It looks to me like you want to join these tables together by their key field and each table's column(s) would be added as needed.  If the key might not exist in a table, you would use a left join (or right join) to that table.
0
 
LVL 8

Accepted Solution

by:
Ganapathi earned 2000 total points
ID: 40021913
I guess this is what you wanted.

The 5 tables you are talking about should have same set of columns. If not, not an issue. Identify the maximum number Columns from the 5 tables. Use default values(Blank or NULL) for the Tables that do not have all the columns(table having maximum number of columns)

Check out the below example. Hope it helps.

CREATE TABLE Table1(DateSpec CHAR(5),Column1 CHAR(5),Column2 CHAR(5),Column3 CHAR(5))
CREATE TABLE Table2(DateSpec CHAR(5),Column1 CHAR(5),Column2 CHAR(5))
CREATE TABLE Table3(DateSpec CHAR(5),Column1 CHAR(5),Column2 CHAR(5),Column3 CHAR(5))
CREATE TABLE Table4(DateSpec CHAR(5),Column1 CHAR(5),Column2 CHAR(5),Column3 CHAR(5))
CREATE TABLE Table5(DateSpec CHAR(5),Column1 CHAR(5))

INSERT INTO Table1 SELECT 'Spec1','Val1','Val2','Val3'
INSERT INTO Table2 SELECT 'Spec2','Val1','Val2'
INSERT INTO Table3 SELECT 'Spec3','Val1','Val2','Val3'
INSERT INTO Table4 SELECT 'Spec4','Val1','Val2','Val3'
INSERT INTO Table5 SELECT 'Spec5','Val1'

SELECT DateSpec,Column1,Column2,Column3 from Table1 UNION
SELECT DateSpec,Column1,Column2,'' as Column3 from Table2 UNION
SELECT DateSpec,Column1,Column2,Column3 from Table3 UNION
SELECT DateSpec,Column1,Column2,Column3 from Table4 UNION
SELECT DateSpec,Column1,'' as Column2,''as Column3 from Table5

Open in new window

Result:

DateSpec Column1 Column2 Column3
-------- ------- ------- -------
Spec1    Val1    Val2    Val3    
Spec2    Val1    Val2            
Spec3    Val1    Val2    Val3    
Spec4    Val1    Val2    Val3    
Spec5    Val1

Note: If Key values are missing in any of the 5 tables, then you will have to go with joins as mentioned by Aikimark.
0
 

Author Comment

by:cansevin
ID: 40022309
Thanks guys. Keys values may possibly be missing. I am going to have to try the Join option. If you can provide any an example of a Join. I don't understand the "Left join" and "Right Join".

There my be situation where a table has all, none or some of the information in the rows.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Ganapathi
ID: 40022332
Check the Result I have given above. Table2 and Table4 do not have all the information, but still they are pulled out with blank information. You may not need Joins here as it included all the results from all the tables.

The tables many need to have the same Key_column names. But they should be of same data types in all the tables.

Refer the below link if you want to know about joins. A good illustration.

http://www.techonthenet.com/oracle/joins.php
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40022360
If you need to join two or more tables, with all rows from one table and just the matching-key rows from the second table, you need to change the join type keyword from a plain JOIN to either a LEFT JOIN or a RIGHT JOIN.

When using the query design wizard, you drag the key field(s) between table listboxes to join them.  You then need to right click on the connecting line and select Join in the pop-up menu.  This will allow you to change the join type.
0
 

Author Comment

by:cansevin
ID: 40022568
Thanks guys... I think I am almost there. I have code for my query below. The problem is, there is SOME data for the row of "LASTWEEK". That data isn't showing up. It seems that only data that is showing is where every column has a value. If only  a couple columns of a row have a value, then I want the row to still show up. Not sure if that makes sense. I have also attached a screen shot of my resulting query and the design view of it.

SELECT DaySpecs.DaySpec, Avg(qryBookingDayswithYearAllAverages.AvgOfAvgOfSumOfExtendedPrice) AS AvgOfAvgOfAvgOfSumOfExtendedPrice, Avg(qryBookingDayswithYearAllAverages.AvgOfCountOfDAY) AS AvgOfAvgOfCountOfDAY, qryBookingTotalsAllSums.SumOfSumOfExtendedPrice, qryBookingTotalsAllSums.SumOfCountOfDAY, qryStatsforDailySalesReportFINAL.SumOfMessagesRecorded, qryStatsforDailySalesReportFINAL.SumOfTotalNewClients, qryStatsforDailySalesReportFINAL.SumOfBookedTrue, qryStatsforDailySalesReportFINAL.SumOfTalkedTo, qryStatsforDailySalesReportFINAL.SumOfFiveMinCallBack, qryReachOutSalesALL.SumOfCountOfCallMade, qryReachOutALL.SumOfCountOfReachOutSent
FROM ((((DaySpecs INNER JOIN qryBookingDayswithYearAllAverages ON DaySpecs.DaySpec = qryBookingDayswithYearAllAverages.DateSpec) INNER JOIN qryStatsforDailySalesReportFINAL ON DaySpecs.DaySpec = qryStatsforDailySalesReportFINAL.DateSpec) INNER JOIN qryReachOutSalesALL ON DaySpecs.DaySpec = qryReachOutSalesALL.DateSpec) INNER JOIN qryReachOutALL ON DaySpecs.DaySpec = qryReachOutALL.DateSpec) INNER JOIN qryBookingTotalsAllSums ON DaySpecs.DaySpec = qryBookingTotalsAllSums.DateSpec
GROUP BY DaySpecs.DaySpec, qryBookingTotalsAllSums.SumOfSumOfExtendedPrice, qryBookingTotalsAllSums.SumOfCountOfDAY, qryStatsforDailySalesReportFINAL.SumOfMessagesRecorded, qryStatsforDailySalesReportFINAL.SumOfTotalNewClients, qryStatsforDailySalesReportFINAL.SumOfBookedTrue, qryStatsforDailySalesReportFINAL.SumOfTalkedTo, qryStatsforDailySalesReportFINAL.SumOfFiveMinCallBack, qryReachOutSalesALL.SumOfCountOfCallMade, qryReachOutALL.SumOfCountOfReachOutSent;

Open in new window

Screen-shot-0425.pdf
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

650 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