Ambiguous outer join
in [Queries]
|
Prev: Nested views
Next: IFF statement Repost
From: Petr Danes on 25 Jan 2010 10:21 I have an update query that joins three tables, A, P & S. S & P are linked with an inner join, P & A with an outer join from P to A. Each join is on three fields, none of which are common between the joins. The query works fine and displays fine in the graphic window, but throws an error message about "The SQL statement could not be executed because it contains ambiguous outer joins." when I attempt to switch to SQL view and throws error 3258 with the same message in VBA when I try to use code to display the SQL. I don't get it - if there's something wrong with the query, why does it work? If not, why won't it show the SQL? I've done all the usual stuff, compact & repair, decompile, copy everything to a new database - no change. Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas.
From: Lynn Trapp on 25 Jan 2010 13:13 Can you post the SQL? -- Lynn Trapp MCP, MOS, MCAS "Petr Danes" wrote: > I have an update query that joins three tables, A, P & S. S & P are linked > with an inner join, P & A with an outer join from P to A. Each join is on > three fields, none of which are common between the joins. The query works > fine and displays fine in the graphic window, but throws an error message > about "The SQL statement could not be executed because it contains ambiguous > outer joins." when I attempt to switch to SQL view and throws error 3258 > with the same message in VBA when I try to use code to display the SQL. > > I don't get it - if there's something wrong with the query, why does it > work? If not, why won't it show the SQL? > > I've done all the usual stuff, compact & repair, decompile, copy everything > to a new database - no change. > > Pete > > > > -- > This e-mail address is fake, to keep spammers and their address harvesters > out of my hair. If you want to get in touch personally, I am 'pdanes' and I > use yahoo mail. But please use the newsgroup when possible, so that all may > benefit from the exchange of ideas. > > > . >
From: John W. Vinson on 25 Jan 2010 16:32 On Mon, 25 Jan 2010 16:21:51 +0100, "Petr Danes" <skruspammers(a)no.spam> wrote: >I have an update query that joins three tables, A, P & S. S & P are linked >with an inner join, P & A with an outer join from P to A. Each join is on >three fields, none of which are common between the joins. The query works >fine and displays fine in the graphic window, but throws an error message >about "The SQL statement could not be executed because it contains ambiguous >outer joins." when I attempt to switch to SQL view and throws error 3258 >with the same message in VBA when I try to use code to display the SQL. > >I don't get it - if there's something wrong with the query, why does it >work? If not, why won't it show the SQL? > >I've done all the usual stuff, compact & repair, decompile, copy everything >to a new database - no change. > >Pete Sometimes if a query has two outer joins - probably especially if they're multifield joins - you'll need to base a query on another query. Create a query joining tables P and S, and then join A *to that Query* rather than trying to do it all in one query. -- John W. Vinson [MVP]
From: Petr Danes on 26 Jan 2010 05:10 > Can you post the SQL? No, I can't. Again: "The query works fine and displays fine in the graphic window, but throws an error message about "The SQL statement could not be executed because it contains ambiguous outer joins." when I attempt to switch to SQL view and throws error 3258 with the same message in VBA when I try to use code to display the SQL." Pete > Lynn Trapp > MCP, MOS, MCAS > > > "Petr Danes" wrote: > >> I have an update query that joins three tables, A, P & S. S & P are >> linked >> with an inner join, P & A with an outer join from P to A. Each join is on >> three fields, none of which are common between the joins. The query works >> fine and displays fine in the graphic window, but throws an error message >> about "The SQL statement could not be executed because it contains >> ambiguous >> outer joins." when I attempt to switch to SQL view and throws error 3258 >> with the same message in VBA when I try to use code to display the SQL. >> >> I don't get it - if there's something wrong with the query, why does it >> work? If not, why won't it show the SQL? >> >> I've done all the usual stuff, compact & repair, decompile, copy >> everything >> to a new database - no change. >> >> Pete >> >> >> >> -- >> This e-mail address is fake, to keep spammers and their address >> harvesters >> out of my hair. If you want to get in touch personally, I am 'pdanes' and >> I >> use yahoo mail. But please use the newsgroup when possible, so that all >> may >> benefit from the exchange of ideas. >> >> >> . >>
From: Petr Danes on 26 Jan 2010 05:17
"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> p�se v diskusn�m pr�spevku news:rd3sl51r21bg7aggobqcb4cemcva8n70ac(a)4ax.com... > On Mon, 25 Jan 2010 16:21:51 +0100, "Petr Danes" <skruspammers(a)no.spam> > wrote: > >>I have an update query that joins three tables, A, P & S. S & P are linked >>with an inner join, P & A with an outer join from P to A. Each join is on >>three fields, none of which are common between the joins. The query works >>fine and displays fine in the graphic window, but throws an error message >>about "The SQL statement could not be executed because it contains >>ambiguous >>outer joins." when I attempt to switch to SQL view and throws error 3258 >>with the same message in VBA when I try to use code to display the SQL. >> >>I don't get it - if there's something wrong with the query, why does it >>work? If not, why won't it show the SQL? >> >>I've done all the usual stuff, compact & repair, decompile, copy >>everything >>to a new database - no change. >> >>Pete > > Sometimes if a query has two outer joins - probably especially if they're > multifield joins - you'll need to base a query on another query. Create a > query joining tables P and S, and then join A *to that Query* rather than > trying to do it all in one query. > -- > > John W. Vinson [MVP] Yes, I can do that, to be sure. But again, the query WORKS. It displays fine in the QBE view, it just won't allow me to view the SQL, either in the query view window or by VBA code. All the parts are there, since it does what it's supposed to. I can add or delete fields in the QBE view and everything continues to work fine. I even deleted all the fields from the query at one point, thinking that I must have had a goofy expression hidden somewhere - still nothing. All is functional, I just can't see the SQL. Pete |