Explanation of "Ambiguous Outer Joins" Error Message

ID: Q124937


The information in this article applies to:

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:
  1. Start Microsoft Access, open a new database, and create the following tables:


  2. 
       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 
  3. View the tables in Datasheet view and add the following data:


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


  2. 
       Include ALL records from 'Consultants' and only those records from
       'Active Consultants' where the joined fields are equal. 
  3. 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:


  4. 
       - 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. 
  5. 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.


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


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

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

  1. Create the following query:


  2. 
       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 
  3. Save and then run the query. The query should return:


  4. 
       ConsID   StartDate   Desc
       -----------------------------------------
       1        7/8/95      ACME Payroll Upgrade
       2        9/1/95      Efficiency Study 
  5. Create the following query:
    
       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:


  6. 
       Include ALL records from 'Consultants' and only those records from
       'ProjectInfo' where the joined fields are equal. 
  7. Save and then run the query. The query should return:


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


Additional query words: inf


Keywords          : kberrmsg kbusage kbdta QryJoin 
Version           : WINDOWS:2.0,7.0,97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 8, 1999