attempting UNION Query to make table 1

[breilly192]

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?

 

[dRahme]

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.

 

[PHV]

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

 

[breilly192]

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