Solved

Access 2003 - SQL Statement Issues - Ambigious Outer joins.

Posted on 2006-07-10
3
769 Views
Last Modified: 2008-01-09
I have the following query :

SELECT DISTINCT comCompanies.comCompany_Name, crpProducts.crpProduct_comCompanyID, crpProducts.crpProduct_Description, comCompanyDivisions.comCompanyDivision_comDivisionID, crpProducts.crpProduct_ID, comDivisions.comDivision_Code, TestDetail.ApplicationNum, TestDetail.TestTime, TestDetail.TestResult, TestDetail.TestType, TestDetail.TestPurpose, TestAgreements.AMCA210UnitType
FROM (((((comCompanies LEFT JOIN crpProducts ON comCompanies.comCompany_ID = crpProducts.crpProduct_comCompanyID) INNER JOIN comCompanyDivisions ON comCompanies.comCompany_ID = comCompanyDivisions.comCompanyDivision_comCompanyID) INNER JOIN comDivisions ON comCompanyDivisions.comCompanyDivision_comDivisionID = comDivisions.comDivision_ID) INNER JOIN crpCheckStatus ON crpProducts.crpProduct_ID = crpCheckStatus.ckstatusProductId) INNER JOIN crpCheckTestData ON crpCheckStatus.ckstatusId = crpCheckTestData.ckdataCkstatusId) INNER JOIN (TestDetail INNER JOIN TestAgreements ON TestDetail.ApplicationNum = TestAgreements.ApplicationNum) ON crpCheckTestData.ckdataTestType = TestDetail.TestType;


I am getting the following error for the above query:

"The SQL Statement could not be executed because it contains ambigious outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then unclude that query in your sql statement."

So then what I did was I devided the queries up into two and here is what I have.

Query one:
SELECT DISTINCT comCompanies.comCompany_Name, crpProducts.crpProduct_comCompanyID, crpProducts.crpProduct_Description, comCompanyDivisions.comCompanyDivision_comDivisionID, crpProducts.crpProduct_ID, comDivisions.comDivision_Code
FROM ((comCompanies LEFT JOIN crpProducts ON comCompanies.comCompany_ID = crpProducts.crpProduct_comCompanyID) INNER JOIN comCompanyDivisions ON comCompanies.comCompany_ID = comCompanyDivisions.comCompanyDivision_comCompanyID) INNER JOIN comDivisions ON comCompanyDivisions.comCompanyDivision_comDivisionID = comDivisions.comDivision_ID;

And the second query:
SELECT DISTINCT crpProducts.crpProduct_ID, crpProducts.crpProduct_Description, comCompanies.comCompany_Name, TestDetail.TestResult, TestDetail.TestType, TestDetail.TestPurpose, TestDetail.TestTime, TestDetail.ApplicationNum, TestAgreements.AMCA210UnitType, comCompanies.comCompany_ID
FROM ((crpProducts LEFT JOIN comCompanies ON crpProducts.crpProduct_comCompanyID = comCompanies.comCompany_ID) INNER JOIN crpCheckStatus ON crpProducts.crpProduct_ID = crpCheckStatus.ckstatusProductId) INNER JOIN (crpCheckTestData INNER JOIN (TestAgreements INNER JOIN TestDetail ON TestAgreements.ApplicationNum = TestDetail.ApplicationNum) ON crpCheckTestData.ckdataTestType = TestDetail.TestType) ON crpCheckStatus.ckstatusId = crpCheckTestData.ckdataCkstatusId
ORDER BY TestDetail.TestResult, TestDetail.TestPurpose;

The only thing with this is that when i create a report for the first query and then a sub report with the second query and create a relationship child and master node join on the crpProduct_ID when I run the report it is like its on an infinte loop it keeps trying to run the queries but getting no where. I have to crash out of it all the time.

Has anyone got any ideas. The crpProduct_ID is the same all around it is a unique identifier, so I don't see why there is an issue here. Any suggestions on getting around this issue? Thanks again in advance for all the help.

0
Comment
Question by:sabrina_spillane
3 Comments
 
LVL 9

Accepted Solution

by:
tculler earned 125 total points
ID: 17074276
In my experience, Access does not allow the mixing of Join types.  In other words, all of the joins in a specific SQL statement must be LEFT, or they must all be INNER.  Even in your attempt to split your operation into two SQL statements, each of the SQL statements still has a mix of LEFT and INNER joins.  That is what is causing the ambiguous outer join error.

Tim
0
 

Author Comment

by:sabrina_spillane
ID: 17074418
Hi Tim,
So could I put the coditions into a where clause or just have to keep make queries until all the joins are either Left or Inner. Do all my queries then for the one report have to be eiher left or inner or can i have one query that is all left and another query that is inner. Any ideas on getting around this.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 17112906
sabrina_spillane,

Here is the microsoft link on "Ambiguous Outer Joins"

Also, as you requested, it does in fact list all of the "Legal" and "Illegal" Join combinations

For example:
   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

Hope this helps as well
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Launch app from Access custom ribbon 8 33
sql statement - 9 24
VBA SQL statement - 2 "OR"s and 1 "And" 4 27
Why get error when delete all records on a sub-form 2 15
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question