Left Outer Join Returns like Inner Join?

[markgrizzle]

HI:
I tried a left outer join on three fields, but the results kept excluding a record from the left table.

In the query designer, everything looked okay, but I ended up modifying the underlying sql statement as shown

The original from clause:
FROM (
tblBatch
INNER JOIN (
tblTemplate
LEFT JOIN
qryPrint ON tblTemplate.PieceFormat =
qryPrint.Format)
AND (tblTemplate.Facility =
qryPrint.FacilityName)
AND (tblTemplate.MediaProgram =
qryPrint.MediaProgram) ...

Final version:
FROM (
tblBatch
INNER JOIN (
tblTemplate
LEFT JOIN
qryPrint ON (tblTemplate.PieceFormat =
qryPrint.Format)
AND (tblTemplate.Facility =
qryPrint.FacilityName)
AND (tblTemplate.MediaProgram =
qryPrint.MediaProgram)) ...


After making this change, the query ran fine, but Access wouldn't display the query in the designer. What I find really frightening is the query designer displaying one thing but doing another.

Has anyone seen this behavior?

 

[PHV]

FROM (tblBatch
INNER JOIN tblTemplate ON tblBatch.SomeField = tblTemplate.SomeField)
LEFT JOIN qryPrint ON tblTemplate.PieceFormat = qryPrint.Format
AND tblTemplate.Facility = qryPrint.FacilityName
AND tblTemplate.MediaProgram = qryPrint.MediaProgram
...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[markgrizzle]

Hi PH:
Thanks for the response. I should have included all the tables in the initial post. I tried your suggestion but got stuck somewhere in the middle. Here's the full from clause:

FROM (tblBatch
INNER JOIN (tblTemplate
LEFT JOIN qryPrint ON (tblTemplate.PieceFormat = qryPrint.Format)
AND (tblTemplate.Facility = qryPrint.FacilityName)
AND (tblTemplate.MediaProgram = qryPrint.MediaProgram))
ON tblBatch.BatchNum = tblTemplate.PrintPO)
LEFT JOIN tblMedia ON tblTemplate.MediaProgram = tblMedia.MediaProgram

 

[PHV]

And where is the problem actually ?
I guess the WHERE clause tests some field of qryPrint or tblMedia and thus defeats the purpose of the outer join.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[markgrizzle]

The query designer was showing each of the three joins between qryPrint and tblTemplate as left joins, but the sql was handling them as inner:

tblTemplate.PieceFormat = qryPrint.Format)
AND (tblTemplate.Facility = qryPrint.FacilityName)
AND (tblTemplate.MediaProgram = qryPrint.MediaProgram)

When I added the extra parentheses, the sql worked properly, but the query designer couldn't display it.

(tblTemplate.PieceFormat = qryPrint.Format)
AND (tblTemplate.Facility = qryPrint.FacilityName)
AND (tblTemplate.MediaProgram = qryPrint.MediaProgram))