UNION query not working

[spam]

If I run the following query in Access 2002 then I get the expected
result:

SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;

I get the contents of both tables with no duplicates. If I add INSERT
INTO, then it doesn't work:

INSERT INTO AssetControl
SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;

I get an error "Syntax error in FROM clause" and it highlights the word
UNION. I have no idea why this isn't working!

Any help would be much appreciated.

Mark

[Keith Wilby]

<sp**@pinkdoggy .net> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

If I run the following query in Access 2002 then I get the expected
result:

SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;

I get the contents of both tables with no duplicates. If I add INSERT
INTO, then it doesn't work:

INSERT INTO AssetControl
SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;

I get an error "Syntax error in FROM clause" and it highlights the word
UNION. I have no idea why this isn't working!

AIUI union queries aren't updatable.

Keith.
www.keithwilby.com

[Anthony England]

<sp**@pinkdoggy .net> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

If I run the following query in Access 2002 then I get the expected
result:

SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;

I get the contents of both tables with no duplicates. If I add INSERT
INTO, then it doesn't work:

INSERT INTO AssetControl
SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;

I get an error "Syntax error in FROM clause" and it highlights the word
UNION. I have no idea why this isn't working!

Any help would be much appreciated.

Mark

Use an alias, U for the union query:

INSERT INTO AssetControl
SELECT U.* FROM
(SELECT * FROM CSVImport
UNION
SELECT * FROM AssetTemp) AS U
The Access query editor may re-write this - note the final dot after the
last square bracket

INSERT INTO AssetControl
SELECT U.* FROM
[SELECT * FROM CSVImport
UNION
SELECT * FROM AssetTemp]. AS U
Other options include saving the union bit as a saved query, and basing your
insert query on this query (rather than the all-in-one solution proposed
above). Another thing to consider would be doing the two inserts separately
and wrapping them in a transaction.

[Anthony England]

"Keith Wilby" <he**@there.com > wrote in message
news:43******** **@glkas0286.gr eenlnk.net...

<sp**@pinkdoggy .net> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

If I run the following query in Access 2002 then I get the expected
result:

SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;

I get the contents of both tables with no duplicates. If I add INSERT
INTO, then it doesn't work:

INSERT INTO AssetControl
SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;

I get an error "Syntax error in FROM clause" and it highlights the word
UNION. I have no idea why this isn't working!

AIUI union queries aren't updatable.

Keith.
www.keithwilby.com

Nobody is trying to update the union query, merely to take the records it
returns and whack them into another table.

[Keith Wilby]

"Anthony England" <ae******@oops. co.uk> wrote in message
news:dr******** *@nwrdmz01.dmz. ncs.ea.ibs-infra.bt.com...

Nobody is trying to update the union query, merely to take the records it
returns and whack them into another table.

I really should stop trying to read in fast-forward mode.