Access confused by its own SQL brackets

[Dan Williams]

I'm pretty new at SQL. I tried this "SELECT DISTINCT FROM (SELECT
UNION SELECT)" structure:

SELECT DISTINCT Firm
FROM (SELECT Firm
FROM [Table of Firms Added to Dropdown]
UNION ALL SELECT Firm
FROM qryFirms) AS T2;

....and it runs fine, but when I go in to look at it again, Access has
changed the ( ) parentheses to [ ] brackets and added a period after
them.

SELECT DISTINCT Firm
FROM [SELECT Firm
FROM [Table of Firms Added to Dropdown]
UNION ALL SELECT Firm
FROM qryFirms]. AS T2;

This is fine. Trouble is, if I make a change and try running it
again, it seems confused by its own nested set of brackets.

Invalid bracketing of name 'SELECT DISTINCT Firm
FROM [Firms Added to Dropdown'.

So I have to put the ( ) parens back and remove the period, and let it
make its changes again, each time I make a change. Is there a
straightforward way to avoid this inconvenience?

Of course, I could rename the table to not require brackets, but is
there a better SQL phrasing? Perhaps one that would not require the
bracketing of the SELECTs?

Access 2000
Windows 2000

Dan Williams
danwPlanet

 

[Noëlla Gabriël]

Hi,

have you already tried to save the union query as a seperate query, like
quniFirm and then build the distinct query like:
select distinct firm from quniFirm as T2?

 

[John Spencer MVP]

There is no way (that I know of) around this problem. Unless you follow the
standard naming convention for fields, queries, and tables (No spaces, no
"special" characters - just Letters, Numbers, and the underscore character)
If you do that then you should still be able to work with Access' funky way of
handling a subquery in the FROM clause.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County