MSA97 syntax error in FROM clause

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChasW - 12 Sep 2006 05:07 GMT

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

Reply to this Message

John Vinson - 12 Sep 2006 06:27 GMT

>SELECT T3.* INTO tblNEW
>FROM
[quoted text clipped - 4 lines]
>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]

Reply to this Message