Combined 'Select' Clause in Access97?

[ting]

Hi, All:

I kept getting "From" clause error, when I tried to
execute such a SQL statement with Access97 Query:
"Select * From (Select * From TableA)"

Actually, I want to run this SQL statement:

Select * INTO TableC From (
Select Field1,Field2,Field3 From TableA Where ...
UNION
Select Field1,Field2,Field3 From TableB Where ...
)

The Union Select was correct in Access97. But the problem
came up when I added the most outter "Select * INTO TableC
From" claus. The Access97 seems not supporting "Select *
From (Select * From TableX)" clause, but Access2000 do!
Anyone knows why? But it works well with Access2000! Can
anyone tell me how to make it proper in Access97? Any
idea highly appreciated!

Ting

 

[Brian Camire]

You're right, Access 97 does not support a SELECT statement in the FROM
clause.

One workaround would be to save the innermost SELECT statement (that is, the
one in the FROM clause) as a separate query (say named "Query1"), and then
change the make table query to something like:

SELECT * INTO TableC FROM Query1