Re: Coping with Ingredients

Hello Graham,

I would recommed something similar to the following:

Add the two fields to tblMaterials
MatIsAdditive - Boolean Used to classify material as an additive if TRUE or base product if FALSE

MatAdditiveKey - Number If MatIsAdditive is TRUE then this denotes the family of additives for this material

                                                     Else this field
indicates the family of additives that is added to this base product
                                                     Default field to 0 to

Add code that checks for MatAdditiveKey > 0 to the procedure that adds records to tblIngredients after a record is saved. If it is greater than 0 AND MatIsAdditive is FALSE then it indicates that an additive could be added to this base product. Store the Qty/square meter is needed of the base material in a variable. Store the MatAdditiveKey from the base material in a variable. Present a dialog with a subform populated with a list of additive materials based on the stored MatAdditiveKey
a text field that stores the percentage of additive needed and Cancel / Add command buttons.
If a user selects an additive, enters a positive percentage and presses the Add command button a process is envoked that adds another record to the ingrediat table that is based on the selected additive and stores the AppRate based on the percentage times the stored AppRate value you stored in the variable earlier.

I hope that this makes sense, it is summer vacation and my kids have been constantly hovering over me as I have attempted to gather and present my thoughts.

Best regards,
Howard

"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
>
>
Received on Wed Jun 12 2002 - 22:39:12 CEST