Microsoft KB Archive/124937

= ACC: Explanation of "Ambiguous Outer Joins" Error Message =

Article ID: 124937

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q124937



Novice: Requires knowledge of the user interface on single-user computers.



SUMMARY
This article discusses the error message:

The SQL statement couldn't be executed because it contains ambiguous outer joins.

This error message occurs when you try to run a query that contains certain combinations of joins. (The three types of joins in Microsoft Access are left outer joins, right outer joins, and equi-joins.) For example, running the following query results in the error message:   Consultants ---> Active Consultants  Projects The SQL statement for this query reads:   Consultants LEFT JOIN [Active Consultants] EQUI-JOIN Projects



MORE INFORMATION
The Microsoft Access Help topic for the error message states:

You tried to execute an SQL statement that contains multiple joins; the results of the query can differ depending on the order in which the joins are performed.

The sample query above (Consultants LEFT JOIN [Active Consultants] EQUI- JOIN Projects) can be processed in two ways. The result of the left outer join between the Consultants and Active Consultants tables can be equi-joined to the Projects table:   (Consultants ---> Active Consultants)  Projects Or, the Consultants table can be left outer joined to the result of the equi-join between the Active Consultants and Projects tables:   Consultants ---> (Active Consultants  Projects) You must specify which method should be used by changing one of the joins or by separating the query into two queries.

The following example demonstrates the different results that a query with ambiguous joins can generate:   Start Microsoft Access, open a new database, and create the following tables:   Table: Consultants -  Field Name: ConsID Data Type: AutoNumber Field Name: FName Data Type: Text Primary Key: ConsID

Table: Active Consultants --  Field Name: ConsID Data Type: Number FieldSize: Long Integer Field Name: ProjID Data Type: Text Field Name: StartDate Data Type: Date/Time Primary Key: none

Table: Projects --  Field Name: ProjID Data Type: Text Field Name: Desc Data Type: Text Primary Key: ProjID   View the tables in Datasheet view and add the following data:   Consultants:

ConsID  FName -     1        Taylor 2       Brad 3       Sharlene 4       Marla

Active Consultants:

ConsID  ProjID   StartDate ---     1        A1       7/8/95 2       D4       9/1/95 3                8/15/95

Projects:

ProjID  Desc -     A1       ACME Payroll Upgrade D4      Efficiency Study 

Method 1: Process "Consultants LEFT JOIN on Active Consultants" First
  Create the following query: <pre class="fixed_text">  Query: AllConsInfo ---  Type: Select Query Join: [Consultants].[ConsID] ---> [Active Consultants].[ConsID] Field: FName Table: Consultants Field: StartDate Table: Active Consultants Field: ProjID Table: Active Consultants NOTE: Make sure that the following property is selected for the join (double-click the join line to check the join property): <pre class="fixed_text">  Include ALL records from 'Consultants' and only those records from 'Active Consultants' where the joined fields are equal. </li>  Save and then run the query. The query should return: <pre class="fixed_text">  FName      StartDate   ProjID -  Taylor     7/8/95      A1   Brad       9/1/95      D4   Sharlene   8/15/95 Marla Note the following items about the query's result: <pre class="fixed_text">  - All the consultants are returned.

- If a consultant is active, his or her start date is returned.

- If a consultant is assigned to a project, the project ID is    returned. </li>  Create the following query: <pre class="fixed_text">  Query: AllConsInfoAndProjects --  Type: Select Query Join: [AllConsInfo].[ProjID] <---> [Projects].[ProjID] Field: FName Table: AllConsInfo Field: StartDate Table: AllConsInfo Field: Desc Table: Projects NOTE: Make sure that the join property is type 1. </li>  Save and then run the query. The query should return: <pre class="fixed_text">  FName    StartDate   Desc -  Taylor   7/8/95      ACME Payroll Upgrade Brad    9/1/95      Efficiency Study </li></ol>

Method 2: Process "Active Consultants EQUI-JOIN on Projects" First
  Create the following query: <pre class="fixed_text">  Query: ProjInfo -  Type: Select Query Join: [Active Consultants].[ProjID] <---> [Projects].[ProjID] Field: ConsID Table: Active Consultants Field: StartDate Table: Active Consultants Field: Desc Table: Projects </li>  Save and then run the query. The query should return: <pre class="fixed_text">  ConsID   StartDate   Desc -  1        7/8/95      ACME Payroll Upgrade 2       9/1/95      Efficiency Study </li>  Create the following query: <pre class="fixed_text">  Query: ProjInfoAndConsultants -  Type: Select Query Join: [ProjInfo].[ConsID] <--- [Consultants].[ConsID] Field: FName Table: Consultants Field: StartDate Table: ProjInfo Field: Desc Table: ProjInfo NOTE: Make sure that the following property is selected for the join: <pre class="fixed_text">  Include ALL records from 'Consultants' and only those records from 'ProjectInfo' where the joined fields are equal. </li>  Save and then run the query. The query should return: <pre class="fixed_text">  FName      StartDate   Desc ---  Taylor     7/8/95      ACME Payroll Upgrade Brad      9/1/95      Efficiency Study Sharlene Marla </li></ol>

Note that the results of method 1 and method 2 differ. When a query is defined like <pre class="fixed_text">  Consultants ---> Active Consultants  Projects Microsoft Access produces an error message because the query can be processed in multiple ways.

The following table lists all the join combinations for the sample query above. Those combinations with "ERR" at the left will cause the "ambiguous outer join" error message: <pre class="fixed_text">  OK : Consultants  Active Consultants  Projects OK : Consultants Active Consultants ---> Projects ERR: Consultants Active Consultants <--- Projects

ERR: Consultants ---> Active Consultants Projects OK : Consultants ---> Active Consultants ---> Projects ERR: Consultants ---> Active Consultants <--- Projects

OK : Consultants <--- Active Consultants Projects OK : Consultants <--- Active Consultants ---> Projects OK : Consultants <--- Active Consultants <--- Projects There are three rules that can be derived from this table:
 * 1) Using all equi-joins in a query is acceptable.
 * 2) Arrows pointing away from the middle table (pointing away from other joins) are acceptable.
 * 3) Arrows pointing toward the middle table require that the other join point in the same direction. (If an arrow points toward another join, that join must point in the same direction.)

Additional query words: the sql statement couldn t be executed because it contains ambiguous outer joins

Keywords: kberrmsg kbhowto kbusage KB124937

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.