Solved

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

Posted on 2006-11-13
3
331 Views
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).

Thanks,
Pete
0
Comment
Question by:PeterWink
  • 2
3 Comments
 
LVL 58

Accepted Solution

by:
harfang earned 450 total points
Comment Utility
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

(°v°)
0
 

Author Comment

by:PeterWink
Comment Utility
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.
Pete
0
 
LVL 58

Expert Comment

by:harfang
Comment Utility
Pete,

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!
(°v°)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now