Solved

Access 2003 - SQL Statement Issues - Ambigious Outer joins.

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

733 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