Incremental Calculation Using Loop (1 Viewer)

ryan180

New member
Local time
Today, 09:53
Joined
Mar 22, 2013
Messages
5
I work for an insurance company where various (and multiple) discounts or loads can be applied to a quoted premium for one reason or another.

These discounts/loads are stored in a table with a corresponding customer ID, where each row represents an individual discount/load (labelled and ordered-by a 'Step').

Unfortunately, the resulting premium from these discounts/loads is not stored in the database and are calculated on the fly by the front-end.

I have a requirement however, to store the 'new' premiums based on the stored discounts/loads for a report.

My problem is that the calculations must occur incrementally one after another, where the discount/load at each 'Step' applies to the resulting premium from the previous calculation.

The attached spreadsheet is demonstrative of the existing table where two additional columns have been added to show you what I need to calculate. 'Price_Amount' represents the discount/load in monetry terms relative to the calculation and 'New_Prem' is the premium resulting from the calculation step.

Notice that each discount/load applies to the previously calculated premium rather than the original one (Original_Prem = the starting point from which all further calculations should apply).

The full table would include many more rows for different customer ID's, where the number of 'steps' could be as few as 1 or as many as 7 per customer ID (ordered by 'Step').

Having toiled for many hours in Access to achieve the above, I am now resigned to the fact that only some kind of VBA function will achieve my requirements.

This function should take the Original Premium for each CustomerID and loop through each 'Step' applying the relative discount/load based on the value in 'Pricing' and the number type in 'Price_Type'. Once the function has calculated each step per CustomerID and has reached the maximum 'Step', it should move on to the next CustomerID.

I am a VBA novice and as such can only articulate the requirements without being able to convert this into actual coded logic. Nonetheless, I am in a bit of a pickle with this one and am under some pressure to create the report. Any assistance as such would therefore be very much appreciated.

I understand that it is not great practice to come on to a forum like this and expect a fully-fledged solution in return, so even some guidance in the right direction would also be appreciated.

Many thanks in advance.
 

Attachments

  • Pricing_Table.xls
    13.5 KB · Views: 147

Rx_

Nothing In Moderation
Local time
Today, 03:53
Joined
Oct 22, 2009
Messages
2,803
See attached Macro enabled Excel workbook

I only have time for the first step.
You will find a function that can work in Excel or in Access.
It basically reads the row columns and creates your result.

For access, your next step is to create a loop with the recordset of one customer.
Then, this record set will use this formula as it moves next, keeping a running summary in a variable.

Please send us your Query in SQL for one customer's record.
Something like Select * from CustomerTable Where Customer_ID = 7
Copy and paste that into Excel with the header names.

The SQL Statement will allow the RecordSet object to be created.
Then the basics of your formula with the records can be created.
 

Attachments

  • Pricing_Table.zip
    12.9 KB · Views: 106

Minty

AWF VIP
Local time
Today, 09:53
Joined
Jul 26, 2013
Messages
10,355
To do this you in access in as simple a fashion as possible you will need to create a query to set up the data for your loop.

The query should simply gather all the customer numbers and the number of steps. Call this something like qryHeader. Two outputs CustID , MaxofSteps/

Use this query to then loop around each record storing the current calculated price in a local variable.

At the last loop around store the final price only on the final step of the matching record. You can then easily query your new table to find the final price.

Edit - sorry bit of duplication, I took ages to write this - then saw the other response.
 

JHB

Have been here a while
Local time
Today, 10:53
Joined
Jun 17, 2012
Messages
7,732
...
I understand that it is not great practice to come on to a forum like this and expect a fully-fledged solution in return, so even some guidance in the right direction would also be appreciated.
You show the result you want and that is fine, but to help you any further we need to see some sample of "raw" data, including the table name and field names, (in a Excel spreadsheet or MS-Access database).
 

ryan180

New member
Local time
Today, 09:53
Joined
Mar 22, 2013
Messages
5
Hi all... apologies for not providing all of the necessary data.

Please find attached an Excel export of my table containing a number of customer ID's and associated calculation steps. This table exists within my Access database and ideally I'd like the VBA function to output an additional 2 fields as per my earlier posted example.

The table is called 'Scenario_Steps' and the field names are as per the spreadsheet.

Hopefully this information will allow for a full solution.

Many thanks.
 

Attachments

  • Scenario_Steps.xls
    15 KB · Views: 119

JHB

Have been here a while
Local time
Today, 10:53
Joined
Jun 17, 2012
Messages
7,732
Run the only query in the attached database.
 

Attachments

  • Calculation.zip
    20.5 KB · Views: 102

JHB

Have been here a while
Local time
Today, 10:53
Joined
Jun 17, 2012
Messages
7,732
You're welcome, luck with it.
 

Users who are viewing this thread

Top Bottom