Join table with multiple/many linked (parent) tables - update error

Posted on 2006-11-13
Last Modified: 2008-03-06
I have 2 tables with a many-to-many relationship (tbl_Item1 & tbl_Errors).  Because of the many-to-many relationship I have created a join/junction table called tbl_Item_Errors.  Using the Access relationship diagram I created the relationships between the 3 tables.  Everything is fine so far.

I have another table, tbl_Item2, which also has a many-to-many relationship with tbl_Errors.  I would like to use the same join table, tbl_Item_Errors, with tbl_Item2.

However, when I try to update the join table with errors relating to tbl_Item2 I get a message saying that a ‘matching record’ for tbl_Item1 must exist in order to write to the table.  I thought it might be an issue with the compound key on the join table, so I got rid of that and just made the key field on the join table an Access auto number field.  However, I still get the same error.

Can someone identify what is causing the error and a possible work around?  I would like to add another tbl_Item3 to match up with tbl_Errors in the same fashion.  I don’t want a join table for each tbl_Item table because I would like all of my errors in one table (tbl_Item_Errors).

Question by:PeterWink
  • 2
LVL 58

Accepted Solution

harfang earned 450 total points
ID: 17935517
Hello PeterWink

What you need seems at first illogical. As you stated yourself, you needed the table tbl_Item_Errors to materialize the many-to-many relationship between the first two tables. If you establish a new many-to-many relationship, you will quite naturally need a new link table. Hence, the only way to create the new relationship to tbl_Item2 is to create a link table, probably called tbl_Item2_Errors.

Think about it in another way: there is a one-to-many relationship between tbl_Item1 and tbl_Item_Errors. This means that for each record in the second table, you need a matching key in the first. When you try to add errors from another table to the link table, this is no longer true, and you get the quite logical error message. Although you can potentially use the same table for several relationships (this has severe limitations), you cannot use the same field to refer to different keys.

If you feel upset about this, it's probably because you perceive that tbl_Item1, tbl_Item2 and tbl_Item3 are somehow connected and should share not only the same errors table, but also the same link table. Formally, there are two solutions:

A) All things are items.

Let's say you have things, gizmos and stuff, but these are all items. You can then have one table called tbl_Items, with an ItemID. You can then create three related tables (one-to-one relationships) where you will find the additional fields needed to describe each type of items: tbl_Things, tbl_Gizmos, and tbl_Stuff. That way, each Thing is also an Item. The many-to-many link between Items and Errors can then work for each item type.

In modelling language, you would say that "a Thing is a kind of Item", "a Gizmo is a kind of Item", etc.

B) Errors can occur for Packs.

Imagine that Item1 are computers, Item2 are docking stations, and Item3 are wireless connections. All can have errors, and are thus collectively related to the errors table. What's more, errors occur for specific combinations of these three item types. You would then create tbl_Items_Errors with four fields: Item1ID (or computerID), Item2ID (or dockingID), Item3ID (or connectionID), and ErrorID (required), materializing the four one-to-many relationships to the four tables. You would then either add an autonumber or three compound keys (depending if the same error can occur more than once for each item).

You will notice that this creates secondary many-to-many relationships between the various Items tables, showing which combined for an error, but with limitations.

C) Item1, Item2, and Item3 are totally distinct objects

In that case, go with three link tables. Why should this be a problem?

Hope this helps


Author Comment

ID: 17938713
Thanks for the thorough response.  It seems I was trying to break a basic fundamental of database design.  However, I thought that by making the key field on the join table an AutoNumber field I would be able to work around any constraints of compound keys.  (Access allows you to bend the rules in some cases) However, it seems that creating a relationship in the relationship diagram creates an inherent requirement similar to that of any normal junction table compound key???

At this point the ‘Item’ tables are already in place, so it seems the proper way to do this is to have separate join tables (Option C).  The items tables (utility equipment) are quite unique; however the errors (rust, lock broken, date inspected) are all similar.  Therefore, it just seemed odd to me to have 3 separate tables (the join tables) in the database with the exact same design.  Three identical tables would seem to defy rules of normalization.

In hindsight it may have been better to have a parent items table and design the database accordingly (Option A).  Unfortunately many of the requirements for this project are being defined as the project evolves which makes it difficult to maintain fundamental principles of design.  If someone new has to support the application in the future I am sure they will be scratching their heads & wondering . . .

Thanks again for the response, I really appreciate it.
LVL 58

Expert Comment

ID: 17938954

Thanks for the grade and for the feedback.

The basic building blocks of a relational database are tables and relationships. As such, the many-to-many relationship isn't: it has to be created with another table. A relationship has a "primary key" side (which is the one side in a one-to-many relationship) and a "foreign key". I believe the fundamental rule you were trying to bend was that you wanted a "foreign key" to have several "primarky keys" in different tables. I.e. the ItemID in tbl_Item_Errors should relate to ItemID in one of three primary tables. That is not possible.

So, yes, a perfectly normalized model would probably go with "X is a kind of Y" rules, implemented as one-to-one relationships (option A). This being said, event if it would be the "best" representation structurally, it is not necessarily the most efficient.

Anyway, good luck with your project!

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question