Re: Coping with Ingredients

Graham:

I suggst you move the AppRate to the Materials table; AppRate is as an attribute of the material. TblIngredients represents a list of the materials applied to each section of the building. TblIngredients is a resolver table for the many to many relationship between TblMaterials and TblSection. You have many materials applied to many sections.

--
                            PC Datasheet
A Resource for Access, Excel and Word Applications
                resource_at_pcdatasheet.com
                    www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
  Synchronize The Data Back To Access Or Excel


"Graham Bellamy" <dontwriteme_at_ask.first.com> wrote in message
news: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

>

>

>