PC Review


Reply
Thread Tools Rate Thread

Make table from UNION queries

 
 
Serge
Guest
Posts: n/a
 
      5th Oct 2004
Hi all,

does anyone now how to create/make table in Access based
on UNION query. I have tried CREATE TABLE or
 
Reply With Quote
 
 
 
 
Roger Carlson
Guest
Posts: n/a
 
      5th Oct 2004
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/...UBED1=ACCESS-L


"Serge" <(E-Mail Removed)> wrote in message
news:0acc01c4aafd$24161010$(E-Mail Removed)...
> Hi all,
>
> does anyone now how to create/make table in Access based
> on UNION query. I have tried CREATE TABLE or



 
Reply With Quote
 
 
 
 
Serge
Guest
Posts: n/a
 
      5th Oct 2004
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
>
>
>"Serge" <(E-Mail Removed)> wrote in

message
>news:0acc01c4aafd$24161010$(E-Mail Removed)...
>> Hi all,
>>
>> does anyone now how to create/make table in Access based
>> on UNION query. I have tried CREATE TABLE or

>
>
>.
>

 
Reply With Quote
 
Serge
Guest
Posts: n/a
 
      5th Oct 2004
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
>
>
>"Serge" <(E-Mail Removed)> wrote in

message
>news:0acc01c4aafd$24161010$(E-Mail Removed)...
>> Hi all,
>>
>> does anyone now how to create/make table in Access based
>> on UNION query. I have tried CREATE TABLE or

>
>
>.
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can 2 Union Queries Be used in Another UNION ?? kev100 via AccessMonster.com Microsoft Access Queries 2 30th Jul 2007 08:38 PM
Union Queries involving 2 Queries Zak Microsoft Access Queries 1 18th Jan 2007 12:44 AM
A union B works, A union B union C fails Phil Smith Microsoft Access Queries 2 29th Nov 2006 06:50 PM
make union query into make-table query =?Utf-8?B?Z2VlYmVl?= Microsoft Access Queries 2 7th Mar 2006 10:27 PM
Refreshing Make Table Queries and Append Queries =?Utf-8?B?SmltbXkgRw==?= Microsoft Access 3 4th Feb 2005 05:25 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:12 AM.