Make table from UNION queries

Discussion in 'Microsoft Access Queries' started by Serge, Oct 5, 2004.

  1. Serge

    Serge Guest

    Hi all,

    does anyone now how to create/make table in Access based
    on UNION query. I have tried CREATE TABLE or
     
    Serge, Oct 5, 2004
    #1
    1. Advertisements

  2. 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" <> wrote in message
    news:0acc01c4aafd$24161010$...
    > Hi all,
    >
    > does anyone now how to create/make table in Access based
    > on UNION query. I have tried CREATE TABLE or
     
    Roger Carlson, Oct 5, 2004
    #2
    1. Advertisements

  3. Serge

    Serge Guest

    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" <> wrote in

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

    >
    >
    >.
    >
     
    Serge, Oct 5, 2004
    #3
  4. Serge

    Serge Guest

    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" <> wrote in

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

    >
    >
    >.
    >
     
    Serge, Oct 5, 2004
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    To union query or not to union query

    Guest, Feb 3, 2005, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    406
    Guest
    Feb 4, 2005
  2. Guest

    make union query into make-table query

    Guest, Mar 7, 2006, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    291
    John Vinson
    Mar 7, 2006
  3. Phil Smith

    A union B works, A union B union C fails

    Phil Smith, Nov 28, 2006, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    549
    Phil Smith
    Nov 29, 2006
  4. Zak

    Union Queries involving 2 Queries

    Zak, Jan 17, 2007, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    241
    Marshall Barton
    Jan 17, 2007
  5. kev100 via AccessMonster.com

    Can 2 Union Queries Be used in Another UNION ??

    kev100 via AccessMonster.com, Jul 30, 2007, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    266
    Guest
    Jul 30, 2007
Loading...

Share This Page