S
Serge
Hi all,
does anyone now how to create/make table in Access based
on UNION query. I have tried CREATE TABLE or
does anyone now how to create/make table in Access based
on UNION query. I have tried CREATE TABLE or
-----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
.
-----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
.