Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: "Ambiguous Outer Joins" Error Message

    I am not terribly adept at access but use it every couple years for fairly simple db's. I was altering this one I use for other purposes. Now, whenever I try to open the 'View Reorts" form, I get the attached error. Can't figure out what changed.

    What do I do next?
    Attached Thumbnails Attached Thumbnails access error.jpg  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It would be easier to help you if you joined the SQL of the query that causes the error.
    Have a nice day!

  3. #3
    Join Date
    Apr 2012
    Posts
    3

    Database error

    I'll attach the database itself and maybe someone can help. The error occurs when I try to execute 'View Reports' and after that error occurs, it brings up the reports window but there are no longer any reports listed.

    Btw, this db was originally a template from the Office website. I was altering it to work for my needs.

    Thank you in advance, this has been driving me nuts. I just want to start using it.
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's your query formulated in a way Access can understand:
    Code:
    SELECT Reports.ReportName, 
           c.ContactID, 
           c.FirstName, 
           c.LastName, 
           c.City, 
           c.CompanyName, 
           c.Title, 
           c.WorkPhone, 
           c.WorkExtension, 
           c.ContactTypeID
    FROM   Reports, (SELECT Contacts.ContactID, 
                            Contacts.FirstName, 
                            Contacts.LastName, 
                            Contacts.City, 
                            Contacts.CompanyName, 
                            Contacts.Title, 
                            Contacts.WorkPhone, 
                            Contacts.WorkExtension, 
                            Contacts.ContactTypeID
                       FROM [Contact Types] RIGHT JOIN 
                            Contacts ON [Contact Types].ContactTypeID = Contacts.ContactTypeID
                    ) AS c
    ORDER BY Reports.ReportName;
    However:
    - Why do you perform a Cartesian product? There is no relationship between the table Reports and the data set resulting from the junction of the 2 other tables (Contacts and [Contact Types]).
    - Why do you perform a RIGHT OUTER JOIN while you do not have any field from the table [Contact Types] in the SELECT part of the query?
    Have a nice day!

  5. #5
    Join Date
    Apr 2012
    Posts
    3

    Database error

    Hey Sinndho, first off, thank you for the help.

    I don't really have much experience with db's. Only what I have managed to teach myself, so some of the things you are saying have gone over my head.

    That query that you attached, do I create a new one with that code in the database?

    I'm not really sure what to do about your suggestions though.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The query I posted is a translation of the original one [Add or Delete Reports] in a form that Access can understand without throwing the error message you mentioned ("Ambiguous Outer Joins").

    What I don't understand (hence my questions) is what you indend to do with the rowset yielded by it, mainly because there is no relationship between the table Reports and the table Contacts. This results in what's called a Cartesian product of both tables (or CROSS JOIN), see: Join (SQL) - Wikipedia, the free encyclopedia) and SQL SERVER 2008 What is a Cartesian product and What causes it Exploring SQL SERVER With Dilip Kumar Jena
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •