Ambiguous outer join

[Petr Danes]

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

 

[Lynn Trapp]

Can you post the SQL?

 

[John W. Vinson]

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.

 

[Petr Danes]

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

 

[Petr Danes]

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.

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

 

[John Spencer]

Can you do the following in the VBA immediate window? I am guessing not based
on your earlier statements, but it does not hurt me to confirm this.

?currentdb.QueryDefs("NameOfTheQuery").SQL

If you get the SQL of the query using that, you can copy it and paste it into
a NEW query to get a clean copy of the query. If that does not work, then all
I can think of is that something has corrupted the querydef.

Which means that after all you have tried you may need to delete this query,
compact your database, and rebuild the query from scratch. Or even tougher
repeat what you have done earlier and import everything with the exception of
this query into a new database.

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

 

[Petr Danes]

Nope, same results - error 3258. I'm not going to sweat it, I got what I
needed the SQL for another way and since the query works in normal use,
that's going to have to do. I have too much other work to pursue this now,
it just irks me, since I see no reason for Access to act like this.

Pete