Keeping Track of Calculated Records when Calculations Change

[wtucker]

Hey Everyone,



I have a question that I'm sure comes up often. In the database I'm designing, we need to keep track of calculations that are individual to each record, mostly fees and commissions, and every once in a while the calculations change (we'll start charging 5% instead of 4%). What is best practice for managing the calculations so that when a change is made, they don't overwrite existing records in which a different calculation was used?

[RuralGuy]

Unless you want to change history, you should keep individual records here and save the results in those records. Calculations should really be done on the fly in queries.

[wtucker]

There's about 300 calculations per client, and 3,500 clients a year, so about 1 million data points just for this one section alone. Calculations generally only change once a year. I was thinking of creating a table that would hold the calculations, adding a new table each year when calculations change, and referencing the appropriate table to display the correct values in the form without having to save each individual value for each client. Thoughts?

[ArviLaanemets]

Are calculation parameters (p.e. fees) same for all clients (or do you have some set of parameters - p.e. fees - which you apply on some condition). When yes, then maybe keep parameters (p.e. fees) history in a tables. P.e.:
tblFees: FeeID, [FeeType], FeePercent, ValidFrom
The date field ValidFrom determines, from which date this particular fee is valid, and the validity continues until a date new fee [of same type] is defined with, or until today. You must define an additional unique index based on [FeeType] and ValidFrom to avoid double entries.
In similar way you design tables for other calculation parameters.

To use such parameter history tables, you design a function with table name, [calculation parameter type], parameter value field name and a date as function parameters, which returns a calculation parameter value at given date. This function you apply everywhere in your calculations instead of fixed parameters (p.e. instead of fee value)

[wtucker]

Calculation parameters can be different for each client depending on the value of other fields. For example, if they select "expedited shipping", we'd tack on $5 to the total bill, which is then used for other calculations.

I really like the idea of a parameters history with validfrom and validuntil fields. Can you elaborate a bit on the additional unique index based on [FeeType]?

If I'm understanding the concept of a parameter history table correctly, it might look like this:

validfrom	validuntil	feetype1	feetype2	feetype3
1/1/2016	1/1/2017	3.00%	4.00%	5.00%
1/1/2017	1/1/2018	3.50%	4.25%	5.00%

From there, I design the function [tblfees], [feetype1], [validfrom]="1/1/2017"
I'm paraphrasing the function design because I don't know how to do it (yet).
I really need to learn how to do multiple IIF() statements in Access the was IFS() works in Excel so I can account for the different possibilities.

[RuralGuy]

How many different fee types do you have and do they all apply to everyone?

[wtucker]

There are probably 20-30 unique fees, and no single fee applies to everyone. Each one will be dependent upon whether boxes on the form are checked or not.

[RuralGuy]

But there could be a table with say 20-30 records that would have all of the fees right? Then checking a box on a form could look up that fee in the table, right? Would that work?

[wtucker]

Yes. It sounds like I'd want to use SQL in the form fields to pull the appropriate value based on the other fields. I think I've got this figured out.

[RuralGuy]

So we are straight: Fields are in Tables, Controls are on Forms. Let us know how you make out and good luck with the project.

[ArviLaanemets]

validfrom	validuntil	feetype1	feetype2	feetype3
1/1/2016	1/1/2017	3.00%	4.00%	5.00%
1/1/2017	1/1/2018	3.50%	4.25%	5.00%

Better is
ValidFrom; ValidTo ; LowerLimit; UpperLimit; Fee
1/1/2016 ; 1/1/2017; 0 ; 100 ; 0.03
1/1/2016 ; 1/1/2017; 100 ; 1000 ; 0.04
1/1/2016 ; 1/1/2017; 1000 ; 10000 ; 0.04
1/1/2017 ; 1/1/2018; 0 ; 100 ; 0.035

And of-course ValidTo is not needed really. The function to return proper fee has to follow the logic (I use a fake query here):
SELECT TOP 1 Fee FROM tblFees WHERE LowerLimit <= [OrderSum] AND UpperLimit > [OrderSum] AND ValidFrom <= [OrderDate] ORDER BY ValidFrom DESC

To design a function in Access, you:
1. Activate VBA Editor (Alt+F11);
2. When you don't have any modules in your project jet (In left upper part of VBA window, expand your project - there must be a tab Modules when you have some module created before), add one (From menu: Insert>Module);
3. Activate the module you want to insert a function in;
4. Create a function in module (From menu: Insert>Procedure and check Function radio button for type, or simply type something like "Public Function MyValue([ParameterList]) As Double" at top, and "End Function" below it). A VBA code is placed between these rows, and before the row with "End Function" (and before any error handlings) you must have a expression "MyValue = [SomeValueCalculatedByVba]", i.e. the function name serves as the variable where you must store the result.

[wtucker]

I think we're a bit mixed up here. The fee types are unrelated to one another, they are not escalated rates based on a shared factor. so feetype1 could be a shipping fee, feetype2 could be a gift wrapping fee, etc.

Better is
ValidFrom; ValidTo ; LowerLimit; UpperLimit; Fee
1/1/2016 ; 1/1/2017; 0 ; 100 ; 0.03
1/1/2016 ; 1/1/2017; 100 ; 1000 ; 0.04
1/1/2016 ; 1/1/2017; 1000 ; 10000 ; 0.04
1/1/2017 ; 1/1/2018; 0 ; 100 ; 0.035

[ArviLaanemets]

This was simply an example, but general principle is same. In your case then table structure will be FeeType, ValidFrom, Fee, and the fee is calculated following logic:
SELECT TOP 1 Fee FROM tblFees WHERE FeeType = [OrderFeeType] AND ValidFrom <= [OrderDate] ORDER BY ValidFrom DESC

Btw., to have calculations simpler, it is always better to keep various types, groups, etc. integer, and to have on forms combo boxes for selecting them. I.e. in your case fee types will be 1, 2, ..., but on form you select and see "Shipping" or "Gift wrapping" or ... in combo.

[wtucker]

This was simply an example, but general principle is same. In your case then table structure will be FeeType, ValidFrom, Fee, and the fee is calculated following logic:
SELECT TOP 1 Fee FROM tblFees WHERE FeeType = [OrderFeeType] AND ValidFrom <= [OrderDate] ORDER BY ValidFrom DESC

Btw., to have calculations simpler, it is always better to keep various types, groups, etc. integer, and to have on forms combo boxes for selecting them. I.e. in your case fee types will be 1, 2, ..., but on form you select and see "Shipping" or "Gift wrapping" or ... in combo.

Thank you so much for the help!