?
Solved

Access 2003 - SQL Statement Issues - Ambigious Outer joins.

Posted on 2006-07-10
3
Medium Priority
?
773 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 9

Accepted Solution

by:
tculler earned 375 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 375 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

771 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