ambiguous outer joins

[libraccess]

The sequel statement could not be executed because it contains ambiguous outer joins.
To force one of the joins to be performed first, create a separate query that performs the
first join and then include that query in your SQL statement
This has frustrated for too long
One main form ,two subforms, both subforms have a FK to get a reference from tblContainer
as soon as I include the tblContainer into the query all I get is this message. I've been reading about Union Queries Left Joins Outer Joins all day long and it's time to seek help
tbls and qry attchdAttachment 6957


thanks
desparate

[June7]

I ran your query and the error does not popup. All the joins are INNER type. I can see how any other jointype would be ambiguous, the relationships go in circles.

Sometimes a single query can't get the output you want. Might require several.

Would you explain what this database is for?

Some sample data might be helpful.

[libraccess]

Hi thanks for your reply. I have the qry working with this
SELECT tblMainDel.DateMain, tblSubTransTo.TransToName, tblSubTransTo.TransToType, tblSubTransTo.TransToVol, tblSubTransTo.TransToDest, tblTransFrom.TransFromName, tblTransFrom.TransFromVol, tblSubTransTo.ContainerRefFK, tblTransFrom.ContainerRefFK
FROM (tblMainDel LEFT JOIN tblSubTransTo ON tblMainDel.IDMainDel = tblSubTransTo.MainTransToFK) LEFT JOIN tblTransFrom ON tblMainDel.IDMainDel = tblTransFrom.MainTransFromFK;

But this does not include the tblContainer it produces the results except I really want the container data included

The Main form is date and supplier details the two subforms are product transfered to a container and product transfered from at the destination. There are only a limited number of Containers (about30) and their ID needs to go onto a report. Bit loathe to post the whole thing for the world to see.

[June7]

Try adding tblContainer to the query twice:

SELECT tblMainDel.DateMain, tblSubTransTo.TransToName, tblSubTransTo.TransToType, tblSubTransTo.TransToVol, tblSubTransTo.TransToDest, tblTransFrom.TransFromName, tblTransFrom.TransFromVol, tblSubTransTo.ContainerRefFK, tblContainer.IDContainer, tblTransFrom.ContainerRefFK, tblContainer_1.IDContainer
FROM tblContainer AS tblContainer_1 RIGHT JOIN (tblContainer RIGHT JOIN ((tblMainDel LEFT JOIN tblSubTransTo ON tblMainDel.IDMainDel = tblSubTransTo.MainTransToFK) LEFT JOIN tblTransFrom ON tblMainDel.IDMainDel = tblTransFrom.MainTransFromFK) ON tblContainer.IDContainer = tblTransFrom.ContainerRefFK) ON tblContainer_1.IDContainer = tblSubTransTo.ContainerRefFK;

[libraccess]

Hi I'm sittng here playing with an idea that if the container ID PK was text (The number/letter of the container I could get it to work and you have already solved the problem
Thats tricky I had not seen the design view built from the SQL. As abeginner it seems to work the other way around
Only one thing now if for a DateMain Entry if there is no tblTransTo or tblTransFrom entry I need to exclude it from my report

Thanks again

[June7]

You don't have to do it in the SQL View window. Just add tblContainers to the query designer from the table list twice and link the fields by click and drag.