I had a good question from Yvonne today, and I thought I'd share it with everyone:

YVONNE:

I am in the process of correcting an existing database.
I have a
ProductT: ?product number, product description, batch size
RawMaterialT: raw material number, raw material description
IngredientT: ingredient description, raw material number, percent
MasterProductT: product number, raw material number, amount

The way the MasterProductT it is currently set is (productA is using these resources in these amounts):

ProductA. ???11111. ???25.000
ProductA. ???22222. ???50.000
ProductA. ???33333. ???15.000

My question: ?once I go in and add the appropriate foreign keys, how do I fix the above scenario? ?Do I have to go in manually and add the foreign key for all the products? ?I'm sure there must be a better way, as I have hundreds of products.

MY REPLY:

Well, I don't fully understand the relationships between your tables by just their names, but if we break it down to just TWO items:

ProductT
IngredientT

Assuming each Product is made up of more than one Ingredient, then YES, you would have to specify which Product each Ingredient belongs to. You can do it manually in the tables or with combo boxes in forms.

ProductT (ProductID, Name)
1, Computer
2, Car
3, Chocolate Cake

IngredientT (IngredientID, ProductID, Name)
1, 1, Hard Drive
2, 1, Memory
3, 1, Keyboard
4, 2, Steering Wheel
5, 2, Gas Pedal
6, 3, Chocolate
7, 3, Eggs
8, 3, Flour

This is a classic ONE-TO-MANY relationship where each product can have many ingredients. Unfortunately, YES, you have to tell Access this manually - it won't just magically know which ingredients belong to which products.

Now if you have a situation where each ingredient could be used in MULTIPLE products, then you need a MANY-TO-MANY relationship. This involves a Junction Table and I cover this in Access Expert Level 7.

For example:

1, Chocolate Cake
2, Vanilla Cake

You might have Eggs and Flour that can belong to BOTH products, but Chocolate only belongs to 1. This requires a many-to-many relationship, so if that's the case WAIT until you get to Access Expert 7 before building your tables.