Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Unanswered: Access sees ambiguities in relationship which I don't think are there

    Hello, colleagues all,

    I attach a Word file with four screen grabs in it.

    The first is the relationship diagram for a digital jukebox content management system.

    The second is the design form for a query involving three tables from the database. As you can see, MS Access says that it cannot create the SQL because of ambiguities in the relationships.

    The third shows the same relationsip diagram as picture 1 after I had saved it and reopened it. MS Access has added some extra tables Track_1, Track_2, Programme_1. Their links to other tables are the same as from the original Track and Programme tables, so I do not know why Access thinks it needs to do this. This may or may not be relevant to my problem but I include it just in case.

    The fourth shows the crux of my problem. As part of my testing I removed each table in the query in turn and found that I still get the ambiguity message even when there are two tables.

    I have searched MS Access Help and MSDN, but cannot find a discussion on the creation of the ghost tables or why a one-to-many relationship should appear ambiguous.

    Any advice from you gurus out there ?

    Salutations
    Jim Wright.
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Play with the join between the two tables...
    It appears to be: One Programme to Many MachineProgrammeList
    (logic says that's correct but it's still worth a try).
    Just change the join type to whatever is available and report back
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Jim Wright
    Any advice from you gurus out there ?
    yes, please show the actual sql that produces that "ambiguous" error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    Quote Originally Posted by Jim Wright
    Any advice from you gurus out there ?
    yes ...
    Self-proclaimed excellence
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Solution but no real advance in understanding

    Thanks for the responses, Colleagues All,

    I was unable to post the SQL because Access said that it could not create it because of the ambiguity. What I did, therefore, was to knock out the Programme table, pick up the SQL code for the remaining limited query and manually apply the join to the programme table. Would you believe it, it works! Furthermore, when you switch to design mode, the graphic representation is exactly the same as for the query which Access says is ambiguous !
    It is interesting to look at the context of this query. The application is a sort of decision support system (DSS) where one can look at the business from various standpoints. To facilitate intervention from these points, I have departed from strict normalisation in two respects. One is that in the hierarchical structure Organisaton (e.g. holding company) -> Site (e.g. Hotel) -> Location (e.g. Function Room), the child tables contain not only the parent foreign key but also the grandparent foreign key and so forth. I also included the code (short name) for grandparents and parents for visual ID. The idea is to avoid more than one level of join in a subform, which Access does not like too much. I renamed the codes by attaching a suffix, because I thought that Access in its wisdom was seeing the codes as foreign keys and asserting another, different, relationship. It could be that something like this hung over even after I had changed the names - though I have to say that nowhere did Access report that the alternate relationship existed.
    For your interest, I attach a Word file with the graphic design and the SQL of the query which worked.
    I am still interested in the alternate problem of the "ghost" tables in the Relationships diagram, which it now appears is not linked to the one just discussed. Any thoughts gratefully received.

    Regards,
    Jim Wright.
    Attached Files Attached Files

Posting Permissions

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