Calculating Totals

[bdhtexas]

I am designing a new database for our life and disability department and they have several different policies where the base life insurance reduces at certain ages.

Please take a look at the scenarios listed below and give me any suggestions and tips on the best way to get this done. If you have any.

Below are the 12 scenarios:

1.) Reduces 75% at Age 70

2.) Age 65 to 70 the life amount is $9,000 and Age 70 and over the life amount is $6,000

3.) Reduces at age 65 by 92%, age 66 by 84.64%, age 67 by 77.869%, age 68 by 71.639%, age 69 by 65.908% and age 70 and over by 40%.

4.) On the day following the 70th birthday, the life amount will reduce to 70%. On the day following the 75th birthday the life amount will reduce to 40% of the 70th birthday reduced amount.

5.) On the Policy Anniversary Date following the 70th birthday - reduces 50%

6.) On the Premium Due Date following the 70th birthday, the life amount will reduce to 70%. On the Premium Due Date following the 75th birthday, life amount will reduce to 40% of the amount of the 70th birthday reduced amt.

7.) Age 65 life amount reduces to 65%. Age 70 life amount reduces to 35%. Age 75 life amount reduces to 25%.

8.) Terminates on the first of the month following the 65th birthday.

9.) Terminates Age 70

10.) Terminates on date of retirement or 1st or month following 70th birthday

11.) Terminates Age 65

12.) Never Reduces

Thanks for your time, Brian

 

[Pat Hartman]

1. Create a new .mdb that links to your existing tables.
2. In the new .mdb, create 11 separate update queries that use the appropriate selection criteria and apply the appropriate rule. To avoid doubling up, I would add a date field to the record and also update that when I applied one of the rule changes. That way your selection criteria can include the RuleChangeDate and only select those which are null.
3. Create a start up form and in its Open event, run the 11 update queries. Be sure to set the warnings off so there won't be any prompts. Set the warnings back on after running the queries and close the database.
4. Put this database on a server which has Access installed and add it to tht windows scheduler to be run at 2 AM each day.

 

[bdhtexas]

Update, I have completed everything above except for #5, #6 and #10...