MSA97 syntax error in FROM clause

[ChasW]

I want to select the contents of 2 different tables into a new table.

I am doing something like:

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

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

Any suggestions would be helpful.

Thank you,
Charles

 

[John Vinson]

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

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

You have to use a very peculiar nonstandard syntax:

SELECT T3.* INTO tblNEW
FROM
[SELECT * FROM TABLE1 AS T1
UNION ALL
SELECT * FROM TABLE2 AS T2]. AS T3;

Note the square brackets, not parentheses, and the period after the
closing bracket; both are essential.

Or... save the UNION query joining Table1 and Table2 as uniBothTables,
and use

SELECT uniBothTables.* INTO tblNew FROM uniBothTables;

John W. Vinson[MVP]