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
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