Results 1 to 6 of 6

Thread: ambiguous outer joins

  1. #1
    libraccess's Avatar
    libraccess is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Napier New Zealand
    Posts
    129

    ambiguous outer joins

    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

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,226
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    libraccess's Avatar
    libraccess is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Napier New Zealand
    Posts
    129
    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.

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,226
    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;
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    libraccess's Avatar
    libraccess is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Napier New Zealand
    Posts
    129
    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

  6. #6
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,226
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Outer Join Composite Key
    By Lady_Jane in forum Queries
    Replies: 6
    Last Post: 08-26-2011, 08:44 AM
  2. LEFT/RIGHT/OUTER JOINS in a set of subqueries
    By luckycharms in forum Queries
    Replies: 1
    Last Post: 08-01-2011, 04:06 PM
  3. Compile Error - Ambiguous Name ???
    By jacek.w.bak in forum Reports
    Replies: 1
    Last Post: 07-07-2011, 08:25 AM
  4. Multiple outer joins - error message
    By Lipi in forum Queries
    Replies: 1
    Last Post: 09-16-2010, 01:44 PM
  5. Ambiguous Outer Join Error
    By scruiks in forum Queries
    Replies: 3
    Last Post: 07-18-2010, 04:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums