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:
- 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 Properties" 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: 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.
- 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).