What is an Ambiguous Outer Join?

[Fspinelli]

Hi All!

I have just received thousands of records which were supplied to me all on one excel sheet.

So I imported the entire spreadsheet into one table of Access and added an ID field set on Auto-number to give each record an ID number. The table has company name, address, some other info about the company, who the contact is (might be more than one), and what type of company they are (to us and could be more than one).

Then I split them up (by copying the same table several times and removing the fields not needed in each table) into the following tables:

1) Company Information (Company name and some other stuff)

2) Company Address (Company address)

3) Contact (Contact person(s))

4) Calls (dates and notes of calls made to the contact at that company)

5) Types (there are 11 - choose all that is applicable - yes/no boxes)

I'm trying to figure out this relationship stuff. It affects joins apparently.

Company Information has a primary key called CoInfoID
note: in the table is CoAddressID (number), TypesID (number) to relate them somehow.

Company Address has a primary key called CoAddressID
note: in the table is CoInfoID (number) and ContactID (number)

Contact has a primary key called ContactID
note: in the table is CoAddressID and CallsID

Types has a primary key called TypesID
note: in the table is CoInfoID

Calls has a primary key called CallsID
note: in the table is ContactID, too.

All have the same (auto)ID number to them because I split the tables up and just renamed the primary key based upon the name of the table. Not the types table though because it's up to the end user who supplied me with this huge file to click on them...

One company may have more than one address and may have more than one contact to each of those addresses. Oh Joy!

Each company will be given certain "types" and the company may have more than one "type" associated to it.

I made a table of Types with auto number TypesID as primary key. Then it has Type and Check (there are 11 types with 11 check boxes next to them). Check all that apply is the basic idea. This is the only table that doesn't have the same primary key ID numbers as the others. It won't until the end user ticks them.

So I have to relate all of the five tables (right?) and I've done this many different ways (reading the three choices of relations it gives you in the Join Type of the Edit Relationships box).

I then created a query "qryMain" to use for other queries I'll need to create - the query has every field of each table in it (but I only put thier primary keys in it).

So in (my) imagination...the theory is that once I click on the Run Query (!) button a table of all records should come up.

But no matter what I do I get the error message:

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

I am trying to avoid writing any code because I'm clueless. But I'm trying to figure out what the error means and fix it (I sort of get it but can't fix it). I've searched but all I can find is Tech Talk and I'm not fluent in Tech Talk.

Once these guys are all joined and happy I will be creating forms that will run other queries such as Types, State or Country, etc. and produce a nice looking result whether by a subform, or a report, or a table. And a data entry form for new records (where a drop down box will show what Companies are currently in the database along with their address and contact person(s). If it's not in there they add a new record which will be that data entry form that takes in the data and dumps them into each table that is related accordingly.

Now that I've rambled on and on... can anyone explain the error message to me in 3rd grader talk (I would say 5th grade but darn...have you watched "Are you smarter than a 5th grader?") As layman as you can get.

Anyway this is how I have them joined in this order:

Types Table: TypesID to TypesID in the...

Company Information Table: CoInfoID to CoInfID in the...

Company Address Table: CoAddressID to CoAddressID in the ...

Contact Table: ContactID to ContactID in the...

Call Table.

Did I explain this enough?

Thank you so very much in advance for your time in reading this and any possible assistance!!!

Have a wonderful weekend!


Kind regards,
Faith

[ADezii]

Just subscribing for now...

[ADezii]

Faith, this is a difficult concept for some Users to comprehend, and an even harder one for some of us to explain. The Error revolves around LEFT and RIGHT OUTER JOINS whereas a Query will return ALL Records from a Table, regardless of whether or not they have matching Records in another Table. These types of Joins are indicated by Right (--->) and Left (<---) Arrows in the Query Desing View for LEFT and RIGHT OUTER Joins consecutively. Access has very stringent Rules when it comes to these kinds of Joins, thus the Error. I realize that I have probably done a horrible job in my explanation, so I created a Demo to illustrate this point. The Demo involves four Tables and the desired result is to return ALL Customers and their Menu selections, even whether or not they ordered something.

1. qryAmbiguousOuterJoin is the logical assumption that you would make in order to achieve the desired results. This, unfortunately, will not work and will generate the Error that you have previous described.
2. qryItems is the first Step you would take in working around this problem.
3. qryCustomerItems is the second, and final, Step that you would take, and will achieve the desired results.
4. Look the three Queries over carefully, then let me know as to whether or not I failed in my attempt as far as an understandable explanation related to the problem at hand.

[NeoPa]

I'm sure ADezii's done a perfectly adequate job of answering the question Faith, but you may want to look at SQL JOINs for more that may help you understand.

In the FROM clause generally, different recordsets can be included. Access doesn't like when recordsets linked by an INNER join are added to the less significant side of an OUTER join. The less significant side would be the side where records needn't exist (The second of two joined by a LEFT join or the first of two by a RIGHT join). This may be a little difficult to follow, so I'll give an example of what Access doesn't like here :

SELECT A.*
     , B.*
     , C.*
FROM   A
       LEFT JOIN
       (B
       INNER JOIN
       C
  ON   B.ID = C.ID)
  ON   A.ID = B.ID

Notice the parentheses surrounding the INNER JOIN (from B to C). This whole block is LEFT JOINed to A, and is on the less significant side. Access would throw a hissy-fit for this.

[ADezii]

@NeoPa:
Something very interesting from the Access 2002 Desktop Developer's Handbook.

Even though ANSI SQL supports the use of parentheses to allow you to arbitrarily combine Outer and Inner Joins in any order, Access does not! This issue arises from the fact that the Jet Query Processor ignores the placement of parentheses when processing Queries. As a result, Access SQL has very specific Rules on how Outer Joins can be combined with Inner Joins or other Outer Joins.

Were you aware of this, NeoPa?

[NeoPa]

No ADezii, I was not.

I don't question the truth of this, but I'm certainly surprised. If I remember correctly, Access itself creates queries with such parentheses in its SQL when using the design grid. Seems weird.

[ADezii]

I'm not buying it either, since the following three SQL Expressions yield three distinct Results. If Parenthesis were ignored, would not the Results be all the same, dictated by Order of Operator Precedence?

SELECT ((100 + 50) * 10 ) /150 As Demo1
'Yields 10
 
SELECT (100 + 50 * 10 ) /150 As Demo2
'Yields 4
 
SELECT 100 + (( 50 * 10 ) /150) As Demo3
'Yields 103.33333...

Oops, looked at the statement a little more closely. It relates only to the combining of OUTER and INNER Joins in any order, which would make a significant difference. In such cases, the above scenario would not apply, I assume.

[NeoPa]

I don't believe the comment pertains to all parentheses in Jet SQL, but just those in the FROM clause. That's how I read it. A test would need to use parentheses within the FROM clause to determine if the author were correct (being conditional rather than past tense). It's also possible of course, that this restriction is now historical as there have been a couple of version releases since then.