Union queries with 3 or more sources question

[spow]

Let's say that I have the following 3 tables or quieries that I want to pull data from:

Table1:
--------
[newproject_name, new_cost]
proj1 10
proj2 11

Table2:
--------
[continuingproject_name, continuing_cost]
proj3 8
proj4 7

Table3:
--------
[project_name, savings]
proj1 5
proj2 6
proj3 4
proj4 3


I want to combine these using a union query to produce this:

newquery
----------
[name ,new_cost ,continuing_cost ,savings]
proj1 10 5
proj2 11 6
proj3 8 4
proj4 7 3

Any help would be greatly appreciated. I know how to combine 2 tables using UNION, but not more than 2.

 

[smedvid]

Remember when using a UNION, the select criteria has to be the same. One trick off the top of my head is to have place holders on tables without the subsequent fields. ie.

Select newproject_name, new_cost, 0.0 as Continuing_cost, 0.0 savings from T1
UNION
Select newproject_name, 0.0 as new_cost,Continuing_cost, 0.0 savings from T2
UNION
Select newproject_name, 0.0 as new_cost, 0.0 as Continuing_cost, savings from T3;


Steve Medvid
"IT Consultant & Web Master"

 

[Kubilus1]

Unioning more than 2 tables is the same as unioning two tables:

Select *
From QueyName1

Union Select *
From QueryName2

Union Select *
From QueryName3

etc.


A union query appends more than one data set with identical column types together, taking the column names of the first data set. I don't believe you want this. If you used a union query you would end up with the following, I believe:

newQuery:
--------
[newproject, new_cost]
proj1 10
proj2 11
proj3 8
proj4 7
proj1 5
proj2 6
proj3 4
proj4 3

Instead I think you will want a standard Select query joining the three tables together on the project_name field.

Matt

 

[BNPMike]

Yep - you need to join the tables. Your problem is you do not have common data throughout so you are going to have to do outer joins. Access doesn't allow full outer joins (only one-way ones) so you may have to build a temporary table over two or more steps or use a combination of joins and unions to collect together all the relevant records.

 

[GingerR]

or you could do one of these two things:

1) in each query add a third field called i.e. "Category".
Re-name the second field in every query ie. "Amount".
in your first query fill this field always with the word "New"
in your second query fill this field always with the word "Continuing"
in your third query fill this field always with the word "Savings"

then do a union query:
Select * from Table1 UNION
Select * from Table2 UNION
Select * from Table3;

you will have:

proj1 10 New
proj2 11 New
proj3 8 Continuing
proj4 7 Continuing
proj1 5 Savings
proj2 6 Savings
proj3 4 Savings
proj4 3 Savings

then you can write a crosstab query on this result:
ProjName Amount Category
GroupBy First GroupBy
RowHeading Value Column Heading

OR 2) make a query: bring in your main table that has the project ID's and information in it. bring in the three tables/queries you mention above. from the ProjectID in the main table, make joins to the project ID's in the three queries/tables so that the arrow is pointing away from the main table. bring down the project ID or name from the Main Table, then bring down NewCost, ContinuingCost and Savings from the three queries/tables. this will give you a list of EVERY PROJECT and it's associated costs and savings from the other tables, even if some are blank.

g

 

[tomschafer]

This is what you want.

SELECT
Table1.newproj_name,
Table1.newproj_cost,
null as contproj_cost,
Table3.savings
FROM
Table1 INNER JOIN Table3 ON Table1.newproj_name = Table3.name;

UNION ALL

SELECT
Table2.contproj_name,
null,
Table2.contproj_cost,
Table3.savings
FROM Table2 INNER JOIN Table3 ON Table2.contproj_name = Table3.name;