Updating Fields in One Table Based on a Field in Another Table

[Odeh Naber]

Imagine this:

You have one table that shows the cost of each ingredient in a
restaurant:

Example:
Tomato 0.45 Euros per kilo
Lettuce 0.60 Euros per kilo

TBLINGREDIENTS
IngredientID
Ingredient
Unit
CostPerUnit

In another table you have the recipe defintion:

Example:
Mixed Salad composed of 400 grams of Lettuce and 200 grams of
Tomatoes.

TBLRECIPEDEFINITION
ProductID
ProductName

TBLRECIPEDETAILS
IngredientID
Ingredient
Quantity
Unit
CostPerUnit (this will fill in automatically using AfterUpdate on the
Ingredient combo field)
ProductID

Eventually the report will give me the total cost for this one dish.

Let us assume now that we have many products that use tomatoes and
cost of lettuce in them - but one day the cost of both will go up.
What I would like to do is to update manually the cost per product in
the TBLINGREDIENTS table - and then with the click of a button have
Access update all CostPerUnit fields in the TBLRECIPEDETAILS where the
cost has actually changed in the TBLINGREDIENT table.

Any advice???

Thank you!!!!

 

[Steve Schapel]

Odeh,

There is a very easy answer to this question. The CostPerUnit field
should not be in the TBLRECIPEDETAILS table. I suggest you remove it.

(Same for the Ingredient field, for that matter.)

It is a fundamental principle of database design that each value should
be stored one time, in one field, in one table. You are flouting this
rule with your setup.

Just leave the CostPerUnit in the TBLINGREDIENTS table. You can easily
retrieve this value, whenever you need it operationally for your forms
and reports and calculations, via a query or in calculated controls.
And then, if the price changes, no need to mess around with updating
data here and there.