Storing a formula in a field

[Guest]

Office Access 2003 -
I am trying to build a database to store inventory/manufacturing information.

I want to store the formula/bill of materials for each product in a field -
the percentages of each material required to make a final product.

For example -
20% x Raw Material 1 + 30% x Raw Material 2 + 50% x Raw Material 3.

Raw Material Info is stored in a products table.

I know you should never store calculations in a field - it being faster to
have that calculation performed when needed. Is this the exception?

Help please - I want to make sure I am starting in the right direction!

 

[Allen Browne]

Presumably the percentage depends on which material it is, so in the
Products table, you would have a field to store the percentage:
ChargeRate Number, size Double, format Percent.

Then you have an InvoiceDetail table that contains one row per raw material.
If the percentages will *never* change, you would base the form on a query
that combines the InvocieDetail table and the Products table, and so arrive
at your calculated result. To me, that sounds like an unsafe assumption: it
is likely that management could change the percentages in the future, and
they would want the old invoices to stay the way they were, so you therefore
need to store the percentages in the InvoiceDetail table (or perhaps just
store the dollar amount that results from the dollar amount multiplied by
the percentage.) You would then assign this value in the AfterUpdate event
of the RawMaterial combo.

For an example of how to assign the amount in the AfterUpdate event of the
combo, open the Northwind sample database, Order Details subform, and look
at the ProductId combo.

 

[aaron.kempf]

what you want to do is to do this in SQL Server Analysis Services.. you
can put an operand in a column and use it with ease.. it's pretty neat
stuff

HTH

-aaron