Explanation of "Ambiguous Outer Joins" Error Message

ID: Q124937

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

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:


   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.

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.

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.

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 Projects" First

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

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

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:


   Include ALL records from 'Consultants' and only those records from
   'ProjectInfo' where the joined fields are equal.

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:

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

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