Ambiguous outer joins. What are they? how to work around?

[Access_guy49]

I have a database in which i am trying to create some forms in order to search for records. The database contains several tables. Some tables are linked using a compound key while others have a single PK. I created the search form which has different searching criteria. Once a user selects the criteria, they hit the search button (there are different buttons for each type of search) the button opens up a form to display all the data. The problem is that there are many tables to report on. I created a form with a subform. The tables associated with the main form are all being retrieved fine. however the subform doesn't seem to work. I created the subform using a query to select all the fields from the desired tables, i gave this query a criteria based on the main form so that the Id's would match. (ie. Main form ID = Firstable in subform.ID)

I got a blank sheet in the subform when i used the search. i presumed that my query was not pulling records because of the referential integrity rules. i went in and changed the relationship rules so that it would include all records from the first table and matching records from the second table. (middle option in the list when editing a relationship) I now get the following error when i try to run the query.

"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement."

Could someone please tell me what this means as the help is... how shall we say it.. written in true Microsoft Fashion.

And though it tells me how to fix it, i'm not really sure what it's telling me to do. I am using Access2003 But the Database is in an access 200 file format.

Thanks.

 

[KeithG]

Your problem is the join between classifications and fish samples on the CA_ID field. It is an outer join and all your other joins are inner join

 

[The_Doc_Man]

I'm going to ramble about on this topic. In general, "Ambiguous outer joins" occur when you try to join (at least) three tables via mutual outer joins and no good relationship exists to help Access decide which one to do first.

See, here's the problem: An inner join finds records with something in common. You get nothing unless both members of the join have something to contribute.

An outer join is different. You get records even if one or more participants has nothing to say on a given record. This has a logical implication: Generally, one of the records is the "main" table that drives the join and the others are of lesser priorities. I'll try to explain by example:

Let's say we have a military person who sits in a particular billet (job) in a particular military unit (sub-organization). We want a list of persons, billets if they have them, and home organizations (if they have been assigned.) New military people, however, might be on a name list but not yet assigned a billet or home unit. (It usually takes a few day for reservists. Trust me on that one.)

If you want a table that shows you everyone, joined or not, you need outer joins that are based on the person table. To ALSO show the billet and unit when either is known, you do outer joins. But now Access will want to know if one of these or the other should be explored first. Because it must first create a recordset with two of the tables before it can join that to the recordset of the third table. Jet really doesn't want to do a three-way join in a monolithic operation. It wants an order of operation so it can try to optimize its actions.

By making a query that joins person to unit FIRST, then joining the person FROM THE person/unit query to the billet, Access knows the exact order in which to do things. And you would get a list of persons, with nulls for unit if they haven't been assigned, and with nulls for billet if THAT hasn't been assigned. Doing it in the other order (person to billet first) would also work. Joining billet to unit would NOT work, though, because the latter pairing would result in some really ugly recordsets. And Access would give you a lot more than you really wanted to know.

The key to outer joins vs. inner joins is to remember that inner joins are a way to reduce the size of the overlap / intersection between two tables. Outer joins are not. Inner joins have an impied "AND" in them - record XXX is in table A "AND" table B. If there is a vacancy in either table for record XXX, it is outta there!

Outer joins have an implied "whether or not" in them. Record xxx is in table A "whether or not" it has a mate in table B. You lose nothing from the "master table" via an outer join. When stated that latter way, you realize that outer joins have a "polarity" of sorts.

Generally, you join the "master table" (the one that contains the records you want to see REGARDLESS of any matches in other tables) to the next most populated table. Then join THAT query the next most populated remaining table, etc. (When in doubt as to which is most populated, either run a quick COUNT query or, if they are really close, flip a coin.)

 

[CraigDolphin]

Thanks for the information Doc_Man. I have often encountered this, and figured out by trial and error how to work around it, but have never really understood the _why_ of it before.

Do you offer a degree program?

 

[Danin]

I have a query that is causing this very problem. i have looked at the joins and none of them are the same.
can't see the trees for the leaves arrghhhh
if it would just tell you which table is causing the problem. i have deleted relationships easier than this in real life :banghead::banghead:

 

[Frothingslosh]

For something like this, you really should start a new thread. A better explanation would really help, as well.

Normally when I run into this error, it's because I have additional joins 'past' the many end of an outer join. SQL Server can process this, but Access cannot.

If I stick with Access, then the solution that has always worked for me is to take everything on the 'outer end' (the table the arrow points to and everything beyond it) and turn it into its own query. Then I do the outer join to that query, and the issue is solved.