Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

attempting UNION Query to make table 1

Status
Not open for further replies.

breilly192

Technical User
Nov 19, 2002
16
US
I'm attempting to use a UNION query to make a table, but keep getting error msg that "An action query cannot be used as a row source.

The code works in Rapid Sql against Oracle tables, but when trying to use it in an Access query's sql it won't work. Tried mimicking other make-table statements that were created by Access but it doesn't like that either with UNION.

The union query will work in Access when not trying to make a table.

Can Union queries be used as make-table queries in Access?
 
Yes, if used from within another query.

Create a new query. Drag in the Union Query. List the out the desired fields and run the make table query.
 
In recent version of access you may try this syntax:
SELECT U.* INTO newTable FROM (
SELECT ...
UNION SELECT ...
) AS U

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Thanks! The way dRahme suggested worked.

I had already tried what PHV suggested but without success in Access 2000. What I ended up doing before reading these posts was to run a single query that created the table, then ran a UNION query similar to what PHV suggested to insert the rest.

INSERT INTO Table
SELECT A....
FROM [SELECT...
UNION
SELECT...
]. AS A
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top