Solved

Access 2003 - SQL Statement Issues - Ambigious Outer joins.

Posted on 2006-07-10
3
772 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 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

724 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