Ambiguous outer join

  • Thread starter Thread starter Petr Danes
  • Start date Start date
P

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
 
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
Click to expand...

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.
 
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
 
John W. Vinson said:
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.
Click to expand...

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
 
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
 
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
 
Back
Top