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