Subquery & Union

[Michael Edison]

I have the two following queries:

** Query1 **

SELECT * FROM
[SELECT * FROM Table1
UNION
SELECT * FROM Table2
]. As Qry1;


** Query2 **

SELECT * FROM
[SELECT * FROM
[SELECT * FROM Table1
UNION
SELECT * FROM Table2
]. As Qry1
]. AS Qry2;


Can anyone explain why Query1 will work with both Access 97 and 2000
but Query2 will only work with 2000? For what it is worth, Access
changes the inside brackets on Query2 to parentheses. Trying the
different combinations of brackets and parentheses did not help. At
the moment we only have Access 97 where I work so a solution that
works with both versions would be ideal.


Thanks for your help,

Mike

 

[John Viescas]

What's the real query you're trying to run? Actually, I'm surprised that
Access 2000 works with Query 2. Access doesn't like brackets inside an
embedded table subquery in a FROM clause - mostly because it adds its own
brackets in non-standard syntax when it stores the SQL and has difficulty
parsing brackets inside of that. It's a kludge that works only some of the
time.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)