Newbie at ADP - Working on problem script (VBA or SP ?)

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I'm working on an Application using AccessXP as front end and MSDE as
backend. To give you a little background info on my project: I need to
evaluate whether it has been a good year (premiums >= claims) or bad year
(claims>premiums) for this member. I have historical data for members claims
in a table called tblHistory. Once I have this data I need to evaluate how
long this member has been Good or Bad which will place them at a certain
Level. This level is used to determine the percentage rate they charged for
premiums against income. Level 1 = Good Mbr for 10 consecutive yrs, Level 2
= Good Mbr for 2 consec yrs, Level 3 = Bad Mbr for 2 consec yrs(, Level 4 =
Bad Mbr for 5 or more consec yrs, Level 5 = bad for more than 10 consec.
yrs. Here's where the real fun starts... BaseRate = 1.3%, Level Mbrs get
discount of .2%, Level 2 get Base rate, Level 3 get penalty increase of .5%
per bad consec yr, if Level 3 has 2 good consec yrs then reduced by .5% per
good consec yr until hit base rate then go to Level2. Level 4 get rate 3.8 %
until 2 consec good yrs.. then drop to Level 3 adn follow level 3 process.
level 5 are risky mbrs and are flagged for review by Higher Ups .... wow !!!
Trust me this wasn't my doing i was given this by the Higher Powers !!!
I have a rough skeleton for the logic needed to evaluate this data but not
quite sure about how to write code.
I started using vba but thought with ADP i can used a stored procedure.
My first question is which would be better ?? I'm thinking sp but not sure?
My second question is does anyone have any pointers or suggestions for this
code? Like i said i'm still hacking my way through this stuff and i need to
come up with something soon. Any help would be appreciated. Leads,
websites, suggestions, etc.
thanks in advance for any help....

rob

Below is my rough idea of vba logic for processing level rates plus sample
data from my history table to give an idea of what my dataset looks like.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1!
Clip of samle data in History table:
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

History_ID,Participant_ID,History_Year,Total_Premiums,Total_Claims
1,35,2002,2330,0
2,35,2003,2272,0
3,36,1981,3957,0
4,36,1982,4144,0
5,36,1983,4249,0
6,36,1984,4527,0
7,36,1985,4651,0
8,36,1986,4465,0
9,36,1987,4466,0
10,36,1988,4533,0
11,36,1989,2905,0
12,36,1990,3049,0
13,36,1991,3001,124
14,36,1992,4710,1118
15,36,1993,4859,0
16,36,1994,4881,0
17,36,1995,4716,60
18,36,1996,4821,0
19,36,1997,4236,337
20,36,1998,4236,0
21,36,1999,4037,576
22,36,2000,4028,192
23,36,2001,4070,0
24,36,2002,3881,0
25,36,2003,5406,816
26,37,2003,2452,0
27,38,2002,416,0
28,38,2003,482,0
29,39,1999,1090,0
30,39,2000,1231,0
31,39,2001,989,0
32,39,2002,1039,0
33,39,2003,1175,0
34,4,2001,846,0
35,4,2002,845,0
36,4,2003,811,0


!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Rough VBA Code to Evaluate history
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Option Compare Database

Private Sub CalcPremium (date currentyear) int

********************
test for level 1
********************

Dim Year As CurrentYear
ConnsecGood = 0


Do
If claims <= premiums
consecgood = consecgood + 1

if consecGood = 10 then exit sub return "1"
Else
consecgood = 0
End If

Year = Year - 1

while year != null and consecGood >0

********************
test for level 5
********************


Year = CurrentYear
consecbad = 0
Do
if claims > premiums
consecbad = consecbad + 1

if consecBad = 10 then exit Sub return "5"
Else
consecbad = 0
End If

Year = Year - 1

while year != null and consecBad > 0

********************
test for level 4
********************

Year = CurrentYear

consecbad = 0
Do
if claims > premiums
consecbad = consecbad + 1
if consecBad = 6 then exit Sub return "4"
Else
consecbad = 0
End If

Year = Year - 1

while year != null and consecBad > 0




********************
test for level 3
********************
Year = CurrentYear

consecBadYearCount = 0

Do
if claims > premiums

consecBadYearCount = consecBadYearCount + 1
if consecBadyearCount = 2 then exit Sub return "3"
Else
consecBadYearCount = 0
End If

Year = Year - 1

while year != null


********************
if all else fails level 2
********************

Exit sub return 2

End Sub

****************************************************************************
*********

Sub CalcLevel3Penalty ( ) float

Year = CurrentYear
rateAdj = 0
cosnsecGood = 0
Do
if claims > premiums
consecgood = 0
rateAdj = rateAdj + penalty

Else
consecgood = consecgood + 1
rateAdj = rateAdj - penalty

End If

Year = Year - 1

while year != null and consecGood <2

if rateAdj <= 0

exit sub return 0
Else

exit sub return rateAdj

End If

End Sub




*************************************************************

********************
test for level 2
********************

Year = CurrentYear
consecgood = 0

Do
if claims <= premiums
consecgood = consecgood + 1
if consecGood = 2 then exit Sub return "2"
Else
consecgood = 0
End If

Year = Year - 1

while year != null and consecgood >0
 
R> Like i said i'm still hacking my way through this stuff
R> and i need to come up with something soon. Any help would be
R> appreciated. Leads, websites, suggestions, etc.
R> thanks in advance for any help....

In other words, you would appreciate if someone made the project for you, so
your client would screw its customers at the full power - 0.2% reward for 10
years w/o claims, 2.5% penalty for 5 years with claims; termination for 10.
Guess anyone will want to review their own claims history before helping you
out - what if it's my own insurance company?

I think it's safer to leave you, your client, and your project in the dark,
where the latter really belongs.

Vadim
 
Back
Top