TheresAlwaysAWay
Programmer
I work for an insurance company and they are changing their commission payment structure. I was given a .pdf grid that contained the values they want to use to determine commission rates. I recreated the .pdf in a table and have included an image of both the .pdf and the table side by side for comparison.
What I'm trying to accomplish is to take a volume value that is generated in a report of total production and find where that numeric value falls in the table. Example would be that one agent had a volume of 143 last month. She is obviously between 140 and 164. The brain does that quite easily, but how do I tell Access to find which table field is the appropriate place? I have added a numeric identifier column to the table, so that <25 = 1, and Between 25 And 39 = 2, etc. I can then use that identifier to determine what the maximum commission to be paid should be. Nothing above level 1 will be considered for anyone with a volume of <25, for example.
The overall commission rate is dependent on both Volume and Close Rate, which is why "<25" is listed twice, as is "Between 25 and 39". I don't want to go too deeply into this whole crazy structure, but if volume <25 and the Close Rate is <40%, they get paid 2%, but if the same volume has a Close Rate of 40% or more they get paid 3%. The words "or more" mean that at that level of volume, even with a 75% close ratio it would still be 3% payment, but if the volume were >195 the payment would be 7% for that close rate.
I just explained that so it would make a little sense, but the real question I have is how I identify the ordinal number that corresponds to a given field value in Access. I can hard-code it of course, but they want to be able to modify those values in the table and have the system respond to any new numbers. That's why I need to figure out how to get Access to identify which group a given field value falls into.
I'm working directly with Admin and I'm confused, so I won't be surprised if others are as well.
Another way to think of it is that the volume determines what the maximum close rate that will be paid. In the earlier example of a volume of 143 her actual Close Rate is 62.8%. But because of her volume the maximum close rate that can be considered for her is 57.5% on the same line with the 140+. She will be paid 4.25%. However, if her close rate had been 51% she would have been paid 3.5%.
I think if someone can show me how to find the Volume in that list I can take it from there and worry about the Close Rate stuff and eventually identify the Commission Rate, so most of this isto give you something to wrap your heads around Bottom line, I'm trying to figure out how to place a field value showing Volume within the parameters shown.
As always, thank you all in advance for your help. I'm pulling what little hair I have out with this one.
What I'm trying to accomplish is to take a volume value that is generated in a report of total production and find where that numeric value falls in the table. Example would be that one agent had a volume of 143 last month. She is obviously between 140 and 164. The brain does that quite easily, but how do I tell Access to find which table field is the appropriate place? I have added a numeric identifier column to the table, so that <25 = 1, and Between 25 And 39 = 2, etc. I can then use that identifier to determine what the maximum commission to be paid should be. Nothing above level 1 will be considered for anyone with a volume of <25, for example.
The overall commission rate is dependent on both Volume and Close Rate, which is why "<25" is listed twice, as is "Between 25 and 39". I don't want to go too deeply into this whole crazy structure, but if volume <25 and the Close Rate is <40%, they get paid 2%, but if the same volume has a Close Rate of 40% or more they get paid 3%. The words "or more" mean that at that level of volume, even with a 75% close ratio it would still be 3% payment, but if the volume were >195 the payment would be 7% for that close rate.
I just explained that so it would make a little sense, but the real question I have is how I identify the ordinal number that corresponds to a given field value in Access. I can hard-code it of course, but they want to be able to modify those values in the table and have the system respond to any new numbers. That's why I need to figure out how to get Access to identify which group a given field value falls into.
I'm working directly with Admin and I'm confused, so I won't be surprised if others are as well.
Another way to think of it is that the volume determines what the maximum close rate that will be paid. In the earlier example of a volume of 143 her actual Close Rate is 62.8%. But because of her volume the maximum close rate that can be considered for her is 57.5% on the same line with the 140+. She will be paid 4.25%. However, if her close rate had been 51% she would have been paid 3.5%.
I think if someone can show me how to find the Volume in that list I can take it from there and worry about the Close Rate stuff and eventually identify the Commission Rate, so most of this isto give you something to wrap your heads around Bottom line, I'm trying to figure out how to place a field value showing Volume within the parameters shown.
As always, thank you all in advance for your help. I'm pulling what little hair I have out with this one.
