How to Update this Field?

[acepeda]

Hey Guys!

Im making a furniture company cost software using MS Access.I have two tables,Product and ProductMaterialDetails.What I want to do is when the user updates a material's price then the product automatically updates its TotalCost.For example if YellowChair uses a galon of Sealer at $5.00 and a galon of Lacquer at $3.50 then the TotalMaterialCost for the YellowChair is $8.50,but if two weeks from now the Sealer goes up to $6.25 then the New TotalMaterialCost for the YellowChair es $9.75,this is what I have:

Table:Products

ProductID         : 504
Description       : YellowChair
TotalMaterialsCost: $8.50
TotalWoodCost     : $23.25
TOTALCOST         : $31.75

Table:ProductMaterialDetails

Reference|ProductID|MaterialID|Description|Quantity|MaterialPrice|Subtotal
   2100     504        1025       SEALER      1         $5.00      $5.00
   2101     504        1041      LACQUER      1         $3.50      $3.50
   2102     505        1041      LACQUER      1.5       $3.50      $5.25

Now what I want to do is change the Sealer's Price to $6.25 and when I press the update button all the products containing Sealer to update its TotalMaterialCost.

I update the materials new price and the subtotal in the ProductMaterialDetails table correctly,my problem is I cant update the TotalMaterialsCost of all the products containing Sealer.How can I make this Update?

[Reverend Jim]

You shouldn't store the cost of materials in the Products table. The fields, TotalMaterialCost, TotalWoodCost and TOTALCOST should be calculated in the query. If you store the actual costs in the Products table then you have to update all of the fields every time a price changes (as you are doing). The only place you should store actual values that could be calculated is in an invoice because you don't want invoice values to change after the invoice is created. The cost of already-made products shouldn't change when the materials that are used change values.