Coping with Ingredients

From: Graham Bellamy <dontwriteme_at_ask.first.com>
Date: Thu, 13 Jun 2002 05:27:25 +1000
Message-ID: <ae87k5$qto$1_at_perki.connect.com.au>

I am applying a surface finish to industrial floors. To an Ingredients table, I add Material and Application Rate. From this (knowing the area) I can calculate the Quantity of each material needed. My problem is where a material does not have its own application rate, but is a percentage of another Material. I don't know how to calculate the quantity of all materials and end up with this info in 'one' calculated field of a query. I believe I need it in just one field so I can later use it in Reports (total job cost, cost of jobs done for Month etc).

Can anyone help?
This is what the structure looks like:

tblMaterials
MatID (pk-autonum)
MatName etc

tblSection (contains properties of the section of the building) SectID (pk)
Description
FloorArea etc

tblIngredients
IngID (pk-autonum)
SectionID (fk 1-M)
MatID (fk 1-M)
AppRate etc

So ingredient data would look like:
IngID SectID MatID AppRate

1001     12           38          0.25 (L/square meter)
1002     12           26          2 (kg/square meter)

Material 38 might be a primer, 26 a resin compound. Knowing the area (say 100 square meters), the Qty is just Area*AppRate. Easy. But now I need to add a pigment whose quantity is 1% of the Qty of Resin. This doesn't fit into my table.
I tried making another table for additives tblAdditives
IngID (fk 1-M)
MatID (fk with tblMaterials 1-M)
Percentage

But then when querying the Ingredients and Additives, I got two Qty columns, and I don't know how to get one column of ingredient quantities (an additive is a kind of ingredient).

Your help in this matter is appreciated. Graham Received on Wed Jun 12 2002 - 21:27:25 CEST