Using UNIONs in a INSERT INTO statement

[rmuller]

In Access (up to version 2002) this does NOT work:

INSERT INTO <table> (<columnlist>)
SELECT <columnlist) FROM <table>
UNION
SELECT <columnlist) FROM <table>
UNION
...

Does anybody know an solution to this problem? (I do not want to use temp tables!)

Greetings
Ronald

 

[smedvid]

Save this as a Query TestQ1:
SELECT TEST1.TEST1, TEST1.TEST2
FROM TEST1
UNION SELECT TEST2.TEST1, TEST2.TEST2
FROM TEST2;

Query to Insert - that referece TestQ1:
INSERT INTO TEST3 ( TEST1, TEST2 )
SELECT TESTQ1.TEST1, TESTQ1.TEST2
FROM TESTQ1;

No way around this undocumented feature of MS Access that I know of. I ran into this many moons ago. htwh, Steve Steve Medvid
&quot;IT Consultant & Web Master&quot;

 

[rmuller]

Thanks Steve,

I am aware of your solution, but i do not want to use an extra query because i use access via odbc.
Or is it possible to create the query outside Accees? I tried:
CREATE VIEW <view_name> AS
SELECT ...

But that does also not work!

BTW Why are so many people using Access, the GUI is great but for the rest ....

tnx,
Ronald

 

[smedvid]

Well, if Access is the backend database you are limited. Not sure if have another backend, like Oracle, which is more robust and should be able to handle that situation.

As for the popularity of Access... It is readily available on the desktop and comes with MS Office. A great marketing ploy by MS. I've used many many desktop DBMS (File Maker, FoxPro,...) and Access has a good bang for the buck.

htwh, Steve Steve Medvid
&quot;IT Consultant & Web Master&quot;