Can't open query in SQL View to correct query ("Join expression not supported")
in [Help]
|
From: David W. Fenton on 27 Jun 2005 21:21 Mike Bridge <mike(a)bridgecanada.com> wrote in news:0b41c1hgrdcdj34ae2pcefm6u19tj0oi5k(a)4ax.com: > Thanks for the tips. Retrieving the query showed that Access had > arbitrarily "fixed" my query by removing some parentheses, and in > doing so it rendered the query invalid and unopenable. Who knows > why it deleted the query contents (thankfully, before I backed it > up)---maybe it was embarrassed at having screwed up my SQL so > badly. Well, maybe you placed your parentheses in a fashion that was ambiguous, or unsupported in Jet SQL. Can you design the same query with the QBE grid and get it to work correctly? I know, when you know SQL well, it seems a pain to point and click, but sometimes SQL that works in other dbs doesn't work unchanged in Access. That works both ways, of course, I have found that Access can mis-guess about parenthese unless you overspecify, putting in all non-necessary ones, just like the QBE designer does by default. Because of that, I basically use the QBE to write all SQL that it can write, and dip into the SQL view only to change what can't be changed in the graphical UI. I've had very little in the way of problems approaching it that way. -- David W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc
From: Mike Bridge on 28 Jun 2005 15:43
Hi- The odd thing is that the query works with the jet engine; Access is just too stupid to parse it---and too stupid not to realize that it shouldn't rewrite things it can't parse. I'm doing a left join with an "AND" in the "ON" statement: SELECT ... FROM a LEFT JOIN b ON (a.id=b.id AND b.somethingelse=a.somethingelse) ....or something to that effect. Access decided that the parentheses were superfluous and removed them. It doesn't bother me that Access didn't understand the query---I could always write it a different way. What does bother me is that Microsoft's basic design is so poor: first off, Access rewrites queries it doesn't understand, and second, it prevents the user from viewing or fixing a query that it can't understand but is working fine (annoyingly, the results show up behind the error message that says that the query is invalid!). And on top of that, it eventually just deletes the contents of queries that can't be opened. I figure that if a product has been around for over a decade and making [m/b]illions in profits, these basic design flaws should be long gone. -Mike On Tue, 28 Jun 2005 01:21:33 GMT, "David W. Fenton" <dXXXfenton(a)bway.net.invalid> wrote: >Mike Bridge <mike(a)bridgecanada.com> wrote in >news:0b41c1hgrdcdj34ae2pcefm6u19tj0oi5k(a)4ax.com: > >> Thanks for the tips. Retrieving the query showed that Access had >> arbitrarily "fixed" my query by removing some parentheses, and in >> doing so it rendered the query invalid and unopenable. Who knows >> why it deleted the query contents (thankfully, before I backed it >> up)---maybe it was embarrassed at having screwed up my SQL so >> badly. > >Well, maybe you placed your parentheses in a fashion that was >ambiguous, or unsupported in Jet SQL. > >Can you design the same query with the QBE grid and get it to work >correctly? I know, when you know SQL well, it seems a pain to point >and click, but sometimes SQL that works in other dbs doesn't work >unchanged in Access. That works both ways, of course, > >I have found that Access can mis-guess about parenthese unless you >overspecify, putting in all non-necessary ones, just like the QBE >designer does by default. Because of that, I basically use the QBE >to write all SQL that it can write, and dip into the SQL view only >to change what can't be changed in the graphical UI. > >I've had very little in the way of problems approaching it that way. |