Join table with multiple/many linked (parent) tables - update error
Posted on 2006-11-13
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).