Date: Tue, 29 Oct 2002 15:55:54 -0600
Reply-To: Rob Rohrbough <Rob@ROHRBOUGH-SYSTEMS.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Rob Rohrbough <Rob@ROHRBOUGH-SYSTEMS.COM>
Subject: Re: (OT) MS Access - save union query results into newtable
Content-Type: text/plain; charset="iso-8859-1"
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
