Make table from UNION queries

  • Thread starter Thread starter Serge
  • Start date Start date
S

Serge

Hi all,

does anyone now how to create/make table in Access based
on UNION query. I have tried CREATE TABLE or
 
The easiest thing to do is create your Union query and save it. Then create
a second query, using the Union query as the table and turn it into a
Make-Table query. For instance:

qryTable123:
SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3;

qmktNewTable:
SELECT qryTable123.* INTO NewTable
FROM qryTable123;

HOWEVER. If you MUST do it in a single query, there IS a way. There is an
*undocumented* format for using a subquery in the From clause - essentially
as another table. It won't work if you have spaces or special characters in
the names of tables or fields (which I don't like anyway). Because of the
syntax, *this* sort of subquery cannot itself contain a subquery of the same
format. The syntax is:

[insert your query here]. As SomeAlias

The left and right square brackets are required, the dot after the right
bracket is required, and the As and alias are required. You can't do
ANYTHING that would require the use of square brackets, inside the square
brackets - that's why no spaces or special characters in names.

Therefore, the single-query syntax for the above would be:
SELECT * INTO NewTable
FROM [SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3]. AS Table123

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thank you Roger for the prompt reply!!!
I will try your solution but I am confident it will work...

Thanks again a million

SR

-----Original Message-----
The easiest thing to do is create your Union query and save it. Then create
a second query, using the Union query as the table and turn it into a
Make-Table query. For instance:

qryTable123:
SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3;

qmktNewTable:
SELECT qryTable123.* INTO NewTable
FROM qryTable123;

HOWEVER. If you MUST do it in a single query, there IS a way. There is an
*undocumented* format for using a subquery in the From clause - essentially
as another table. It won't work if you have spaces or special characters in
the names of tables or fields (which I don't like anyway). Because of the
syntax, *this* sort of subquery cannot itself contain a subquery of the same
format. The syntax is:

[insert your query here]. As SomeAlias

The left and right square brackets are required, the dot after the right
bracket is required, and the As and alias are required. You can't do
ANYTHING that would require the use of square brackets, inside the square
brackets - that's why no spaces or special characters in names.

Therefore, the single-query syntax for the above would be:
SELECT * INTO NewTable
FROM [SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3]. AS Table123

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe? SUBED1=ACCESS-L


Hi all,

does anyone now how to create/make table in Access based
on UNION query. I have tried CREATE TABLE or
Click to expand...


.
Click to expand...
 
Great Roger,

Thanks it works!!!
-----Original Message-----
The easiest thing to do is create your Union query and save it. Then create
a second query, using the Union query as the table and turn it into a
Make-Table query. For instance:

qryTable123:
SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3;

qmktNewTable:
SELECT qryTable123.* INTO NewTable
FROM qryTable123;

HOWEVER. If you MUST do it in a single query, there IS a way. There is an
*undocumented* format for using a subquery in the From clause - essentially
as another table. It won't work if you have spaces or special characters in
the names of tables or fields (which I don't like anyway). Because of the
syntax, *this* sort of subquery cannot itself contain a subquery of the same
format. The syntax is:

[insert your query here]. As SomeAlias

The left and right square brackets are required, the dot after the right
bracket is required, and the As and alias are required. You can't do
ANYTHING that would require the use of square brackets, inside the square
brackets - that's why no spaces or special characters in names.

Therefore, the single-query syntax for the above would be:
SELECT * INTO NewTable
FROM [SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3]. AS Table123

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe? SUBED1=ACCESS-L


Hi all,

does anyone now how to create/make table in Access based
on UNION query. I have tried CREATE TABLE or
Click to expand...


.
Click to expand...
 
Hi Roger,

I used the code you provided to create a table from Union Query in a single query. Now, I'm trying to use this table to join with another table that I created with same way. I get the error that says "Query must have at least one destination field". Could you please help out!
 
Back
Top