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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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