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.
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
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.