query 2 tables into 1 table

[ChasW]

I want to create a new table with the contents of 2 other tables whose
design specs are the same.

What would be the syntax to do this?

Right now, to get 1 query's results into a table I do something like:
SELECT tbl.* INTO tblNEW FROM tbl;

but what about from 2 sources? Is there a way to use UNION ALL in the
above statement?

Regards,
Charles

 

[Smartin]

I want to create a new table with the contents of 2 other tables whose
design specs are the same.

What would be the syntax to do this?

Right now, to get 1 query's results into a table I do something like:
SELECT tbl.* INTO tblNEW FROM tbl;

but what about from 2 sources? Is there a way to use UNION ALL in the
above statement?

Regards,
Charles

Sure,

SELECT T3.* INTO tblNEW
FROM
(SELECT * FROM TABLE1 AS T1
UNION ALL
SELECT * FROM TABLE2 AS T2) AS T3;

HTH

 

[ChasW]

Sure,

SELECT T3.* INTO tblNEW
FROM
(SELECT * FROM TABLE1 AS T1
UNION ALL
SELECT * FROM TABLE2 AS T2) AS T3;

HTH

This works in Access 2000, but in Access 97 I am getting "Syntax Error
in FROM clause"

How do I go about doing this for Access 97?

 

[Smartin]

This works in Access 2000, but in Access 97 I am getting "Syntax Error
in FROM clause"

How do I go about doing this for Access 97?

Hmm. I don't have access to A97 at the moment. Hopefully someone else
knows? Otherwise I will take a look next week.

 

[Smartin]

This works in Access 2000, but in Access 97 I am getting "Syntax Error
in FROM clause"

How do I go about doing this for Access 97?

It appears the work-around in A97 is to put the subselect in a separate
query ...

QUERY GET_ALL_TABLES
====================
SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2 ;


.... And call it from the make-table query...

QUERY MAKE_NEW_TABLE
====================
SELECT * INTO NEWTABLE
FROM GET_ALL_TABLES;