Access confused by its own SQL brackets

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

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

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?
--
Kind regards
Noëlla


"Dan Williams" wrote:

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

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

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

Dan Williams wrote:
I'm pretty new at SQL. I tried this "SELECT DISTINCT FROM (SELECT
UNION SELECT)" structu

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