I have a MS Access database with three tables:

Foods (Id, Food_Name, Food_Desc)
Ingredients (Id, Ingredient_Name)
IngredientsFoods_mm (Id, Id_FoodsFK, Id_IngredientsFK)

...in a many-to-many relationship, where IngredientsFoods_mm is the table that links the other two.

Now, I made a form with a subform to assign ingredients to foods. In the main form there are the Food_Name and Food_Desc fields and in the subform there is the Ingredient_Name field (a combo box). Seems to work well, but the problem is that when I enter an ingredient that is already in the Ingredients table, instead of assign the appropriate record, it creates a duplicate record.

I tried to avoid this by setting an index without duplicates in the Ingredient_Name field, but then Access tells me that that would create an integrity error.

The question is, how can I avoid the creation of duplicate records in the Ingredients table when entering info from the subform?


I think your problem is that you are trying to insert into Ingredients table when you are supposed to Insert the relationship between Foods.Id and Ingredients.Id through the IngredientsFoods_mm table.

Make sure your relationships are well defined and that the IngredientsFoods_mm's Primary Key is formed from both Id_FoodsFK and Id_IngredientsFK.

In here is the explanation of relationships in access. It covers up how to make up a correct many-to-many Relationship model. https://support.microsoft.com/en-us/kb/304466

  • I do think that the relationship are well defined and the PK in the Id_IngredientsFK is already formed by the Id_FoodsFK and Id_IngredientsFK (I've already read the link you provided and yep, the DB have that same structure). The thing is that there are already some records in the Ingredients table, but I wanna assign them to or create new records and link them to the Foods table. – Kureno Sep 8 '15 at 5:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.