A bug in GUI of creating query with more outer joins between two tables

  • Thread starter Thread starter Ivan
  • Start date Start date
I

Ivan

Hello,

in GUI of Access 2007 (12.0.6423.1000) SP2 MSO (12.0.6521.5000) is
generally very simple to make a select query between two tables joined with
two or more outer joins (of the same kind normally).

But you should be careful. If you build first join clicking on a field of
the first table, drag then the connection to the field of the second table
and choose the right outer join, then you have to make the next outer join
between the same two tables on the same way. If you begin but with the
second table first and although you choose the same type of outer join (the
arrows are oriented to the same table) you get the error message: "The SQL
statement could not be executed because it contains ambiguous outer join. To
force the one of the joins to be perormed first, create a separate query
that performs the first join and then include that query in your SQL
statement." if you try to run the query.

You could not see even the SQL View. If you try to change the view you get
the same error message!

Is there anyone with the same experinece?

Greeting

Ivan
 
If I understand you, this is GUI of your query --

Table1 arrow
Table3
Table2 arrow

I assume it will not know which happens first.

Try using a union query like this --

Table1 arrow Table1 arrow Table3
union
Table2 arrow Table2 arrow Table3_1

Left join union to Table1 left join to Table3
Left join union to Table2 left join to second instance of Table3

Hope you follow this. If not then post back and I will do the SQL.
 
I fear you didn't understand me.

I have spoken only about two tables, where there are two or more outer joins
(arrows) between these two tables.

You can create these outer joins (equally oriented arrows) graphicaly using
mouse. And all these arrows must be created starting from the same table.
Namely although you can in principle build graphicaly the equally oriented
arrow starting from the opposite table and declaring orientation of the join
in Join Properties window, you get after such action but the error message.

Ivan
 
Yep, given two 'lines' between tableA and tableB, both lines must be of
the same type, ie, both without arrows (inner join), or both with an arrow
pointing toward the same table. While it does not make sense to have two
lines with arrows each pointing to different table, in this scenario, since
which table would get all its records preserved, returned, in the result?

A fourth possibility, a full outer join, is generally pictured (when
supported) by two arrows, one at each end of the line representation of the
join. ***IF*** this is what you try to do, full outer join are not directly
supported by Jet, while they are with MS SQL Server, so for Jet, you have
to use one of the possible works around.


Vanderghast, Access MVP
 
The problem is given two tables with a multi-field OUTER join.

Drag from tableA Field1 to TableB Field 1 and set the join (all records in A
and matching in B) (LEFT JOIN)

Drag from tableB Field@ to TableA Field @ and set the join (left Join) (all
records in A and matching in B) (RIGHT JOIN)

This means you have ambiguous joins between the tables. The Arrows in the
graphical interface will both point from TableA to TableB.

Whether or not this is a bug depends on your point of view. The obvious thing
to do is to always drag in the same direction when setting up a multi-field
outer join.

In Access an Inner Join will work (although the On clause looks a little
weird). Not the reversal of tables in the following SQL statement.

SELECT *
FROM TableA INNER JOIN TableB
ON TableA.Field1 = TableB.Field1
AND TableB.Field2 = TableA.Field2

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

I think You only have understood the point of my writing. Most likely I was
also too careless in the representation. :-(

Why I think that such graphical construction is nevertheless problematic?

Most newbies make queries with help of GUI. And although after the (second)
draging from tableB to tableA it is subsequently defined, that all record
from tableA are included and only the matching of tableB (ie. left join) and
even the arrows in the graphic are equally oriented, the join is in the
truth the right join!? A frustration for a newbie!

Thanks

Ivan
 
Back
Top