asked on

Union Query

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!

aikimark

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.

ASKER CERTIFIED SOLUTION

Ganapathi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cansevin

ASKER

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.

Ganapathi

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

aikimark

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.

cansevin

ASKER

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