Best aproach for Calculations

[William Demuth]

Hi

I need advice. Just a sense of direction really. Should I use a calculated field on a table, or a seperate query?

I need to perform calculations and show the results in a form.

I have a table called recipies that lets users select ingredients from several different tables, and then a quantity and unit of measure like ounce or cup for each ingredient added.

These units of measure are in a table that offers their equivalent value in Millilitters

I need to show total ML on the form

I need to take each ingredients quantity, and multiply it by its ML equivalent and then add them up.

Whats the best approach?

 

[plog]

Calculations should be done, not stored. Use a query.

 

[NeutronFlux]

It's best to use a query. If your outcome can be reproduced from the data you have at any time, it's better not to store it. First, it bloats the database. Second, if some of your data (eg. ingredient quantity) changes then those stored values are no longer correct. There are special occasions when you might want to store calculated data, such as if the results come from very heavy calculations that take quite some time to accomplish and you're fairly certain the input data is unlikely to change, but this shouldn't be the case.

 

[William Demuth]

Thanks

I have tried, but no luck

I have a total of 12 ingredient fields each with a unit of measure

Query works fine with one item, if I add a second I get ambiguous joins.

I tried relationships, and tried joins from each Unit of measure field but nothing is worrking.

I suppose 12 seperate query's would work, but that seems inefficient

What am I missing?

 

[Rabbie]

It sounds as if your data is not normalized. But without seeing your DB it is not possible to be sure.

I would advise you to read up on data normalization if you have any doubts on this score.