Date:         Tue, 29 Oct 2002 22:14:26 +0000
Reply-To:     alejandro.jaramillo@ATT.NET
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         alejandro.jaramillo@ATT.NET
Subject:      Re: (OT)  MS Access - save union query results into newtable

Doug,
You have to create the union select query first, let's called qry_union.
This is how it will look like:
Select [table1].* from table1
union select [table2].* table2
union select [table3].* table3;

Then create a make table query that will be base on qry_union.
This is how the sql will look like:

SELECT [qry_union].* INTO YOUR_TABLE_NAME
FROM [qry_union];

YOUR_TABLE_NAME is the name that you decide to give to your access table.

Hope this helps.

alejandro


> Doug, have you tried comp.database.ms-access at Google Groups?
> (http://groups.google.com)
>
> FWIW, I think the Access SQL is a little squirrly.  Try something like
> (untested):
>
> create table xyz as
> select numericol_1
> from   table1
> UNION
> select numericol_2
> from   table2
> ;
>
> It just sticks in my mind that Access doesn't support the INTO keyword.  I'm
> sure there is a SQL way to do it.  However, if all else fails the only other
> alternative I can think of is to write some VB DAO or ADO code to read the
> tables and do the "union" by copying each existing table into the new one.
> If not already on your bookshelf, I recommend Sybex's "Access Developers
> Handbook" by Getz, Litwin, et al.  They have editions from Access 2 through
> Access 2000.  About a year ago, they were still working on 2002, and that
> likely is out now too.  Head and shoulders over any other reference I have
> seen.
>
> Another site to try is http://www.mvps.org/access/index.html.
>
> HTH,
> Rob Rohrbough
>
>
> -----Original Message-----
> From: McAllaster, Douglas L. LTC [mailto:mcallasterd@LEE.ARMY.MIL]
> Sent: Tuesday, October 29, 2002 2:18 PM
> Subject: OT: MS Access - save union query results into newtable
>
>
> Folks,
>
> I want to save the results of a union query into a new table with MS Access.
>
> select numericol_1
> from   table1
> UNION
> select numericol_2
> from   table2
>
>
> Normally, MS Access uses syntax:
>
> select column1, column2, ...
> INTO newtable
> from oldtable
> where clause
>
> However, I cannot get the
> INTO newtable
> syntax to work with the UNION.
>
> I've tried creating an empty table (using create table)
> then using the
>
> INSERT
> INTO
> select numericol_1
> from   table1
> UNION
> select numericol_2
> from   table2
>
> but that fails, too.
>
> The MS Access help doesn't help & neither do my two reference texts.
>
> My only success has been to run the union query
> & then use the pull down menu options save as
> but of course a non-programmatic solution is no solution as far as I'm
> concerned.
>
> Thx,
> LTC Doug McAllaster
> McAllasterD@Lee.Army.Mil