Updating data in table B based off table A - Duplicates

[klutz2009]

Hello All,

This is my first post, sorry if it's in the wrong thread.

I need help understanding how to accomplish the following in Access 2007.

I have two tables, Table Products & Table Sold and I'm trying to aggregate the products table to reduce the total number of products and I want this update to happen with all tables that share a 1 to many relationship with the product table. (table sold is one of those)
So let's say I have products apple, pear, bananna. I am now aggregating them to all just be called fruit. Problem is when I make this change in the product table I get this error:



"The changes you requested to the table were no successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

So I'm not sure how to go about aggregating data in a table that has 1 to many relationships with many other tables. A
I would appreciate any help at this point. Thank You!

[rzw0wr]

Can we see your tables and relationships.

Dale

[klutz2009]

rzw0wr, I've attached a picture of the tables with the data and the relationship between the two.

Essentially what I'd like to do is change the Apple, Grape, & Pear all to "fruit" in the product table. My question is, once I change all of these products to "fruit", how can I make sure that the change translates to the Sold table. Since Product is the primary key in the product table, I'm unable to change all three (Apple, Grape, and Pear) to fruit because then I would have duplicates and Access won't allow that. That is where I get the above mentioned error.