Calculated Field Scenario, Should I?

[kd2017]

First and foremost I know it's best generally speaking to not use calculated fields. Would the following scenario be an exception you could live with?

(calculated field in the sense that results are saved to a numeric datatype field with vba and not using the actual calculated field datatype in the table design)



I've got a basic table called Price_Items with fields: Item_Desc, Item_Quantity, Unit_FK, and Unit_Price

This is a vanilla quantity * unit_price = total price scenario, total price WILL be queried based off those two fields. I would like to create a number of different unbound dialog forms, calculators, that the user can use to calculate the value of the Item_Quantity field. Different forms are necessary because depending on what the item is there is a different way to estimate the quantity. For example if the item is a sheet good then the quantity might = length*width, if it's a linear good then quantity=2*(length+width) OR qty=len_1+len_2+...+len_n OR qty=roundup(len/spacing+1), etc... As you can see there are many different formulas used to calculate quantity so I haven't found a good way to normalize this. Sometimes the quantity doesn't need a calculator at all.

I would also like to add a 2 more short text fields to the Price_Item table: Item_Quantity_Calculator and Item_Quantity_Vars. Item_Quantity_Calculator would be the name of the calculator form used to come up with the qty, and Item_Quantity_Vars would be a comma deliminated list of variable values that the calculator used to calculate the qty. This way if the user needed to edit the qty later each form could parse the Quantity_Vars field and populate itself.

Bad idea?

[Edit] Item_Quantity_Calculator would be a foreign key referencing to a specific calculator form.

[orange]

Can you describe the application generally in 2 or 3 lines, plain English?
Customer-Orders-Products; OrderDetails; Invoicing????
Lot's of free data models here.

[kd2017]

Produces price estimates for construction services. At it's simplest level a price estimate simply equals the sum of line item quantities * unit prices. Provide the user with several unbound calculators to calculate the estimate quantity field when appropriate BUT the calculator can still recall and adjust the calculation later.

Some calculations need 2 variables, some need a dozen variables, all use different formulas to estimate quantity so it can't be normalized so results must be "precalculated" and stored (As far as I can tell).

[orange]

Can't say I have ever dealt with calculations needing a dozen variables, but I would say build a small demo and see if it will do what you need? Perhaps start with a few variables; Test it thoroughly, adjust as needed; then see if you can expand the pattern to a larger number of variables. Do you need to record actual values as well as estimates?
You may find something via google/bing or in Similar Threads at the bottom of the page.
Good luck.

[kd2017]

Ok thank you. I just wanted to make sure the idea wasn't an overwhelming hard no.

It might be better to create a child table with generic key -> value pair fields instead of the comma deliminated string of values.

[orange]

I wasn't sure of the csv string, but if it something you "feel" would be useful, I'd try it. It may help your analysis and you may find it's what you need, or prove to yourself --there's got to be a better way. Either way, you'll learn something related to your project.

[ArviLaanemets]

You can consider a design, where:
tCalculationGroups: CalculationGroupID, CalculationGroupName, Parameter1, Parameter2, ... (the max number of parameters you have to decide yourself), where in fields Parameter# you enter the name of parameter for this calculation group. E.g. you may have calculation groups like
1, "Pieces", "pcs", Null, Null, Null;
2, "Length", "Length", Null, Null, Null;
3, "Weight", "Weight", Null, Null, Null:
4, "Rectangle area", "Length1", "Length2", Null, Null;
5, "Round area", "Diameter", Null, Null, Null;
6, "Parallelepiped", "Length", "Width", "Height", Null;
etc.

Then you must have items table like
tblItems, ItemID, ItemName, ItemUnit, CalculationGroupID, Parameter1, Parameter2, ... (where into fields Parameter# you enter numeric values for parameters needed for this calculation group).

Now whenever you want to calculate quantity for this item, you can run a procedure were you use e.g. If statements like

...
If CalculationGroupID = 1 Then
     <calculations>
ElseIf CalcullationGroupID = 2 Then
     <calculations>
...
End If

[kd2017]

So would you use a VBA function in queries to calculate the quantities on the fly or would you use the function to precalculate the qty and save it in a field in the tItems table?

Here is a model of what I think you've described:


And here is a model that kinda tries to normalize it:


Thoughts?

[kd2017]

I've got a semi related question:

Which of these two would be more efficient for access to process?:

iif(B=True, A*C, A) AS total_calc

VS

A*(B*(1-C)+1) AS total_calc

Both of these formulas will return the exact same result. The latter code is more convoluted at first glance but it doesn't have to call an Iif function which I believe I read can be a little taxing in large queries.

[Bullschmidt]

Nobody else answered. Don't know specific answer, but perhaps you could test if you have a lot of records and my guess is that any difference would be negligible. Microsoft does say that IIf() can sometimes be relatively inefficient and/or a problem if divide by 0 for example in that BOTH parts of the function get tested no matter what.

But I have something I sometimes call "Developer Sanity" that I like to think about for looking at my code later and/or if someone else ever took over one of my projects. And to me your first example looks WAY more readable than the second.

And one more thing you might want to consider is that every time you multiply or divide you may want to use rounding, otherwise it's possible to have data STORED like 3.412578 even though with Currency 2 formatting it may still just LOOK like 3.41.