MS Access: SQL - ambiguous outer join - nested query?

Hi, looking for some help with a query which is used as a recordsource for a form which is called from another form as follows:

DoCmd.OpenForm stDocName, , , "[inpInstallNumber]=" & Me.INSTALLATION_ID, , , OpenArgs:=strParameters

The SQL view of the query looks like this (I've stripped out a bunch of fields from each table for readability):

SELECT Installs.enaType, [Install Checklist].inpInstallNumber, [Install Checklist].inpStartDate, Processes.proName
FROM Installs INNER JOIN ([Install Checklist] INNER JOIN Processes ON [Install Checklist].inpProID = Processes.proID) ON Installs.enaInstall = [Install Checklist].inpInstallNumber;

The problem is that on the form I need to be able to enter a value in the enaType field which is used to create records in the [InstallChecklist] table but, since there are initially no records where Installs.enaInstall = [Install Checklist].inpInstallNumber, I can't enter anything in that field.

What I want to do (back to the query design view here) is set the join properties to "Include ALL records from Installs and only those records from Install Checklist where the joined fields are equal". However, when I do that I get the "ambiguous outer join" message. That (broken) SQL looks like this:

SELECT Installs.enaType, [Install Checklist].inpInstallNumber, [Install Checklist].inpStartDate, Processes.proName
FROM Installs LEFT JOIN ([Install Checklist] INNER JOIN Processes ON [Install Checklist].inpProID = Processes.proID) ON Installs.enaInstall = [Install Checklist].inpInstallNumber;

I'm not sure how to do this by creating a separate query as suggested by Access. So I also started down the path of using an SQL string for the recordsource from VBA. I did this from the form load event using the SQL generated from the query design view (in other words with no changes) and saw a significant degradation in the screen display as compared to when the query was used as the data source for the form. So I had to abandon this approach.

Is there a way to perhaps create this query as a nested SQL statement?

Thanks!
michaelcpaulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
When you do a join A to B to C if A to B is a LEFT OUTER JOIN then B to C also has to be a LEFT OUTER JOIN.
Cheers, Andrew
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
All your "downstream" tables must use the same sort of join ... try setting the same characteristics on both your Joins and see what happens.

However, unless you've setup your query correctly you may have issues with adding data through this query ...
0
michaelcpaulAuthor Commented:
I appreciate the suggestions but as I am very weak with creating SQL queries I need a pretty specific example directly related to my scenario.
0
TextReportCommented:
OK see below, Cheers, Andrew
SELECT Installs.enaType
     , [Install Checklist].inpInstallNumber
     , [Install Checklist].inpStartDate
     , Processes.proName
FROM Installs 
    LEFT OUTER JOIN ([Install Checklist] 
        LEFT OUTER JOIN Processes 
        ON [Install Checklist].inpProID = Processes.proID) 
    ON Installs.enaInstall = [Install Checklist].inpInstallNumber;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
michaelcpaulAuthor Commented:
Andrew, that was exactly what I was looking for! Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.