UNION subquery fails in INSERT statement

[Guest]

I can't get an INSERT statement to accept a UNION. Is this a fact or am I
really missing something here? Here is an air-example.

Should something like this work?

insert into mytable (myfield) select field1 from table1 union select field2
from table2


Thanks,
David

 

[Guest]

Create a UNION Query and then run the INSERT on this Query
Query 1
select field1 from table1 union select field2 from table2

Query 2
insert into mytable (myfield) select field1 from Query1

 

[John Spencer]

This test query worked for me in Access 2003

INSERT INTO IntegerTable_pop (IntegerFIeld)
SELECT IntegerField
FROM
(SELECT IntegerTable.IntegerField
FROM IntegerTable
WHERE integerField <=10
UNION
SELECT IntegerTable.IntegerField
FROM IntegerTable
WHERE integerField Between 20 and 30) as I

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..