Trying to incorporate a Union query in an Append query

[Dor100]

I'm trying to create an append query out of a union query. The union query alone works fine, but I need to call it over and over again from code to insert it's output into a new table, i.e., db.execute "insert into Table select...union select...;" (I definitely need to do this, the union query itself will not accomplish what I need.) It's not working. I keep getting the error msg "missing operator," but I've been unable to determine the problem, or if this is even possible. Seems like it should be.

Thanks,
Dor100

"If you care diligently for the alien embryos implanted within you, your position will be great in the new administration of this planet."

 

[Guest_imported]

A union query cannot be an action query. You need to save the union query, then run your action query off of it (as you would a table). If the union query needs to be dynamic, you will have to use dao to recreate the union query with the new parameters over and over.

-SnakeDog

 

[jhall156]

The only way I've been able to do this is to create the union query and save it and then create the insert query
like:

INSERT INTO finaltable
SELECT fieldlist FROM unionquery
JHall

 

[mscraig]

You might try putting your UNION SQL into a Query Definition. Then use the query definition as the record source (like a table) for your Append/Insert SQL.
Union statements generally don't allow much going on other than the Union other than something like an Order By.

 

[jhall156]

This might work for you. It creates a new table though.


SELECT fieldlist INTO newtable
FROM
SELECT fieldlist FROM table1
UNION
SELECT fieldlist FROM table2

of course the usual UNION caveats apply. JHall

 

[Dor100]

Just got in. Thanks a lot, people.