ACC: Explanation of "Ambiguous Outer Joins" Error Message

Last reviewed: May 7, 1997
Article ID: Q124937
The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

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:

  1. 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
    
    

  2. 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

  1. Create the following query:

          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.
    
    

  2. Save and then run the query. The query should return:

          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:
    
        - 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.
    
    

  3. Create the following query:

          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.
    
    

  4. Save and then run the query. The query should return:

          FName    StartDate   Desc
          -----------------------------------------
          Taylor   7/8/95      ACME Payroll Upgrade
          Brad     9/1/95      Efficiency Study
    
    

Method 2: Process "Active Consultants EQUI-JOIN on Properties" First

  1. Create the following query:

          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
    
    

  2. Save and then run the query. The query should return:

          ConsID   StartDate   Desc
          -----------------------------------------
          1        7/8/95      ACME Payroll Upgrade
          2        9/1/95      Efficiency Study
    
    

  3. Create the following query:

          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.
    
    

  4. Save and then run the query. The query should return:

          FName      StartDate   Desc
          -------------------------------------------
          Taylor     7/8/95      ACME Payroll Upgrade
          Brad       9/1/95      Efficiency Study
          Sharlene
          Marla
    
    
Note that the results of method 1 and method 2 differ. When a query is defined like

   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:

   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).


Keywords : kberrmsg kbusage QryJoin
Version : 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbinfo


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 7, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.