Union Query between 2 or more Queries

[Tfrup12]

Is it possilbe to create a Union between two or more queries? I have created
Union query between multiple Tables but haven't had any luck creating a union
between other queries. If yes, could someone please share an example of the
SQL that would be used to make such a query?

TIA!

 

[Jerry Whittle]

It should be as simple as something like below IF all the fields in both
queries match up. If they don't match up, you will need to specify the field
names in the correct order.

SELECT * FROM qryA
UNION
SELECT * FROM qryB ;

 

[John Spencer]

Yes it is possible, but the query fields have to be the same type and same
number of fields.

SELECT ColumnA, ColumnB, ColumnG
FROM qSel_8ColumnQuery
UNION
SELECT ColumnX, ColumnD, ColumnC
FROM qSel_26ColumnQuery

As long as
ColumnA and ColumnX are both the same type (Text, Number, Date) and the
other column pairs also match up this query should run.

Now if you tried to UNION a Crosstab query, I am not so sanguine about
things working out.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Tfrup12]

Thank you Jerry! I have it working now. ...Btw, I like your quote from Mr B.

 

[Tfrup12]

John, Thank you for your help too. Its much appreciated!

 

[Netdogs]

This was the most adequate place to put this I'm crossing my fingers some one
will read this.

I have two 3 tables,
1st table. Main categories
2nd table Sub category
3rd table Item.
From the item, I can select and see the Main Category and Sub Category from
a look up colum, But once select it I can only see the value the ID of the
Subcategory. 1-4. Since I only have 4 right now.
How can i get it to show me Main Category, SubCategory.
Here is my query.

SELECT TableSCategory.SubCategory, [TableMcategory].[Mcategory] & " " &
[TableSCategory].[SubCategory] FROM TableMCategory INNER JOIN TableSCategory
ON TableMCategory.ID=TableSCategory.LookUpMainCategory ORDER BY
[TableMcategory].[Mcategory] & " " & [TableSCategory].[SubCategory];

Thank you so much

 

[John Spencer]

If I understand what you want to do then try changing the query join to a LEFT
JOIN from an INNER JOIN.

SELECT TableSCategory.SubCategory
, [TableMcategory].[Mcategory] & " " & [TableSCategory].[SubCategory]
FROM TableMCategory LEFT JOIN TableSCategory
ON TableMCategory.ID=TableSCategory.LookUpMainCategory
ORDER BY [TableMcategory].[Mcategory] & " " & [TableSCategory].[SubCategory]

By the way this is not a UNION it is a JOIN. A UNION query would combine
records vertically from two or more queries or tables. A JOIN combines
records from two tables into one row - horizontally.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County