JOIN expression not supported - need help figuring out what to move to a different query

[Montez659]

I am getting the "ambiguous outer Join" message on the following:

SELECT JUNCtblAudit_AuditItems.auditDingsID, JUNCtblAudit_AuditItems.subCatID, JUNCtblAudit_AuditItems.noLongerValid, JUNCtblMainCat_SubCat.mainCatSubCatID, JUNCtblMainCat_SubCat.mainCatID, tblAudits.auditID, tblAudits.auditorName, tblAudits.auditDate, tblAudits.auditProvName, tblAudits.auditProvID, tblAudits.dateAuditRecd, tblAudits.dateRepCompleted, tblAudits.dateRepRecd, tblAudits.auditType, tblAudits.repAudited, tblAudits.delEntity, tblAudits.newHireAudit, tblAudits.auditID, tblAudits.extraNotes, IIf([newHireAudit]=True,"New Hire Audit","Regular Audit") AS switchIt
FROM (tblSubCat INNER JOIN (tblMainCat INNER JOIN JUNCtblMainCat_SubCat ON tblMainCat.mainCatID = JUNCtblMainCat_SubCat.mainCatID) ON tblSubCat.subCatID = JUNCtblMainCat_SubCat.subCatID) INNER JOIN (tblContacts INNER JOIN (tblAudits LEFT JOIN JUNCtblAudit_AuditItems ON tblAudits.auditID = JUNCtblAudit_AuditItems.auditID) ON tblContacts.contactID = tblAudits.repAudited) ON tblSubCat.subCatID = JUNCtblAudit_AuditItems.subCatID
WHERE (((JUNCtblAudit_AuditItems.noLongerValid)<>True));

And I can't quite figure out what I need to separate out and create a different query with. I thought it was pretty straight forward (at least visually) and I have a hard time seeing it in SQL.

I am trying to Join tblAudits to JUNCtbl_Audit_AuditItems on auditID, and select all records from tblAudits, but only the corresponding records from the Junction table. I am not able to select that option (number 2 in the Access dialog box). I can only select the option where there are records in both.

Any direction is always appreciated!

 

[boblarson]

Ambigous outer join errors mean that you are trying to get data from essentially tables which are not set up so that the particular joins can happen all at the same time. So, the fix for that is to create a query with one of the outer joined tables (or the bunch of them that are all flowing in the same direction) and then use that query in another query where you can then include the other table(s) with the direction that you want with them.

 

[Norie]

Montez

You're trying to do too much in one query.

Try splitting it out into more than one, that will be easier to debug, the queries might even be quicker and the separate queries might have their own uses.

 

[Montez659]

Ok, I was playing around with it and I got everything to the right of tblAudits boiled down into a single query, so I was able to select option 2 to show all records from tblAudits and only those from qryAllDings (which encompasses everything to the right from the last SQL) where the joined fields are equal. However, I am still only coming up with three records which have Audit "dings" and I am not seeing all the records.

Not to mention there is at least one instance of a record with dings that is not showing up. I am thoroughly confused. I guess I will just have to play around with it for a while.

 

[Montez659]

Ahh, well, there must be something wrong in my formulas or join or something because I just found that I am pulling information that is not even associated with a record into the report under that record ID. I will research further.

 

[Montez659]

Just to update and let the world know that I am a moron. For some reason when I built the report, I built it with the main grouping level by Rep as opposed to auditID, so it was thoroughly confusing stuff when trying to run the query. After all that got fixed, the Join's seem to be working properly.

 

[boblarson]

Just to update and let the world know that I am a moron. For some reason when I built the report, I built it with the main grouping level by Rep as opposed to auditID, so it was thoroughly confusing stuff when trying to run the query. After all that got fixed, the Join's seem to be working properly.

Don't be too hard on yourself. I think we've ALL done similar stuff at one time or another. It happens.

 

[David Wayne]

I am trying to create a form in MS Access that allows the user to select a detective and multiple case statuses (such active, inactive, dna, filing, etc.). The form is intended to allow the user to determine the number of assigned cases. The form is tied to a query and the criteria that I've set up in the query is: [Forms]![DET_CaseManagementFRM]![Combo30] which is under the investigator field. The case status field has similar criteria [Forms]![DET_CaseManagementFRM]!
[List43].

My problem is that I cant figure out how to allow the user to select multiple criteria in case status field in my form. The research I've done online says you need to specify simple or extended in the Multi Select field under the other tab in the property sheet. However, when I do specify simple it doesnt return any records which I know it not true. Is there VB code that I need for this?