Calculate Totals Based on Selections from Combo Boxes

[LAM]

I am looking for some direction on how to set up the following.
We need to compute a final premium amount based on multiple factors.
The Gross premium is a given number, we then need to calculate several subtotals based on a varying number of debits and credits (each premium can have one to twenty or more debits and/or credits) to arrive at the Net Premium.

Simplified Example:
Gross Premium $1,000
Less Factor 1 $100 (based on 10% discount)
Plus Factor 2 $50 (flat fee)
= Subtotal One $950 (gross premium +/- chosen factors)

Subtotal One $950
Less Factor 3 -$25
Less Factor 4 -$10
=Subtotal Two $915 (sub-total one +/- chosen factors)

Subtotal Two $915
Plus Factor 5 $200
Plus Factor 6 $20
=Net Premium $1,135

Each Subtotal can include a different number of factors to calculate the next subtotal. Each Net Premium can be the result of a different number of subtotals.

So on the form I would like the user to enter the Gross Premium and then select the factors from a combo-box (user's need to be able to add the value of a factor because it can be different for each premium calculation), have the form calculate the first subtotal. Then select the next set of factors to calculate the second subtotal.

The caluclation then needs to be "saved" so users can refer back to it. Ultimately I also need to create a "what if" form so that users can see how a change to one factor will affect the net premium. Not all of these "what If" calculations need to be saved, but we need the option to do so if necessary.

I know this is a lot and I am willing to do as much as I can on my own, but need a good reference or a starting point to begin. I am not even sure what to search for!

Any ideas will be appreciated.

LAM

 

[MarkK]

This looks to me like a problem for Excel. A database system shines when it can handle large numbers of the same types of things regardless of the order in which they appear. Particularly if they all are related to a single type of thing that is like a parent. If you have one customer who might place many orders you want a database. Then you can get out information on how much a customer spent in March or how regularly you sell a Filbert Flange or what your sales were to Wisconsin.
The tool you seem to be looking for doesn't--IMO--have enough structure to be represented easily in a database.
If your 'subtotals' represented a certain specific category of 'factors', maybe. If certain 'factors' could only contribute to certain 'subtotals', maybe. Then the database becomes a system that manages the relationships between the categories (subtotals) and factors that are available in only those categories.
But that's not what your post seems to be driving at. See what other people think, but I'd be looking at Excel.

 

[LAM]

Thanks for the input. This calculation would only be a portion of a larger database that we use to track a major business process including contracts, clients, etc. Currently all of this is being done in Excel and it is very cumbersome, especially since so much of the information is used over and over. Using a database should eliminate much of the double, triple, quadruple, etc., entry of data that is done now.

I believe that the factors will affect only certain subtotals, but I am not certain of that so wanted to, hopefully, build something more dynamic. I do know that not every premium will have all factors. That is why I am hoping to create something where we can choose which factors apply. New rules are constantly being issued so we need the capability to "add" factors to our list as they are published.

I am working on a solution and will post back if/when I have made some progress. In the mean time does anyone else have any suggestions?

LAM

 

[PNGBill]

What about a Single Form that has the 'Contract" Number/Name as the Record Source.

The Controls on the Form are set out in 3 or four columns.

Each column consists of Bound and unbound controls.
As you go down the column you either Type in a value or if a combo box select a value and some of the controls are calculated to be add or subtract as you proceed.

The bottom control is the net result.

The columns allow the options.

VBA will save the chosen option to file as the historical record.
This presumably would require a command button for each of the columns and the code behind this button only referred to the control names in that column.

20 options by 4 columns is 80 which should not stress the limit of the form.

Have a warning message with the possible data being saved displayed when the button is clicked to avoid the wrong button and once confirmed, close the form and open the Contract for any final confirmation.

 

[Poppa Smurf]

Taking PNGBill suggestion further you could use TAB or Pages on the form for each step to avoid the appearance of a cramped form.

Regarding your "What if" we developed something similar at work where the user made a selection from the historical data this included today and earlier records. The selected historical data was stored in a temporary table so that it did not corrupt or change the "live data". The user then made changes as required, clicked on the refresh button to show the updated changes. There were buttons to Print, preview the screen in a report format. Another button to refresh the screen with the original data.