Microsoft KB Archive/208878

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

Article ID: 208878

Article Last Modified on 6/30/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208878



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 equi-joins, left outer joins, and right outer 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 </ol>

Method 1: Process "Consultants LEFT JOIN on Active Consultants" First
<ol>  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):

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: <ul> All the consultants are returned.</li> If a consultant is active, his or her start date is returned.</li> If a consultant is assigned to a project, the project ID is returned.</li></ul> </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 Properties" First
<ol>  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: Consultants Field: Desc Table: ProjInfo

NOTE: Make sure that the following property is selected for the join:

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:
 * Using all equi-joins in a query is acceptable.
 * Arrows pointing away from the middle table (pointing away from other joins) are acceptable.
 * 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 kbinfo kbusage KB208878

-

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

© Microsoft Corporation. All rights reserved.