How do I union tables together into a new table?

[jt]

I wrote and can run this querry:

sELECT a.*
from students_r8793 a
union all
select b.*
from students_r8940 b
UNION ALL select c.*
from students_r8941 c;

runs fine and gets the correct results but when I try to create a new
table with the results by doing

sELECT a.* into students_r8793_and_r8940_and_r8941
from students_r8793 a
union all
select b.*
from students_r8940 b
UNION ALL select c.*
from students_r8941 c;


I get an error "An action query cannot be used as a row source" and I'm
sure what this error message is telling me.or how to do what I want to
do.

Thanks for any quidence.

Jack

 

[Guest]

If you really need to create a table rather than just returning the result
set of the UNION ALL operations whenever necessary parenthesize the UNION ALL
operations like so:

SELECT *
INTO students_r8793_and_r8940_and_r8941
FROM
(SELECT *
FROM students_r8793
UNION ALL
SELECT *
FROM students_r8940
UNION ALL
SELECT *
FROM students_r8941);

You don't need to use aliases for each table.

Ken Sheridan
Stafford, England

 

[John Spencer]

I'm not sure this will work, but you could try

SELECT TMP.* into students_r8793_and_r8940_and_r8941
(SELECT A.*
from students_r8793 a
union all
select b.*
from students_r8940 b
UNION ALL select c.*
from students_r8941 c) as TMP

Otherwise, you could save the first query and use it or you could use three
queries to append data into a pre-defined table. Or one make table query
and two append queries.

 

[Jack]

Thanks all

Jack