If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Access confused by its own SQL brackets
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 |
#3
|
|||
|
|||
Access confused by its own SQL brackets
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 |
Thread Tools | |
Display Modes | |
|
|