R
rwinans
I have created a database to track sales of a product and to determine
commissions for the sales team. Each quarter I print a report outlining the
commission scales for each salesperson. This scale differs based on how many
sales were made the previous quarter. I would like to have a database table
(tblVipScale) that defines the commissions mathematically (scale base, scale
increment, commission base, commission increment and maximum commission).
From a form, I have a command button that creates a report for each sales
rep. I want that command button to be coded to also create a table based on
the information in tblVipScale for use in the report, sort of like a make
table query. I am not very good at coding, but below I have outlined what I
need to do. Can someone please help with a create table command?
*tblVipScale contains fields ScaleName (Primary Key), ScaleBase,
ScaleIncrement, CommissionBase, CommissionMax, CommissionIncrement*
If CommissionIncrement = 0 Then
Calc = 1
Else
Calc = ((CommissionMax – CommissionBase) / CommissionIncrement) + 1
ScaleVar = 0
CommissionVar = 0
Build a table called tblScale with fields Scale and Commission
Loop 1 to Calc
ScaleLow = ScaleBase + ScaleVar
ScaleHigh = ScaleLow + ScaleIncrement – 0.1
Scale = ScaleLow & “ – “ & ScaleHigh
Commission = CommissionBase + CommissionVar
Populate tblScale with Scale and Commission
ScaleVar = ScaleVar + ScaleIncrement
CommissionVar = CommissionVar + CommissionIncrement
End Loop
Example of a result would look like this:
Scale Commission
100-104.9% 1%
105-109.9% 2%
110-114.9% 3%
Thanks in advance for any suggestions.
Rick
commissions for the sales team. Each quarter I print a report outlining the
commission scales for each salesperson. This scale differs based on how many
sales were made the previous quarter. I would like to have a database table
(tblVipScale) that defines the commissions mathematically (scale base, scale
increment, commission base, commission increment and maximum commission).
From a form, I have a command button that creates a report for each sales
rep. I want that command button to be coded to also create a table based on
the information in tblVipScale for use in the report, sort of like a make
table query. I am not very good at coding, but below I have outlined what I
need to do. Can someone please help with a create table command?
*tblVipScale contains fields ScaleName (Primary Key), ScaleBase,
ScaleIncrement, CommissionBase, CommissionMax, CommissionIncrement*
If CommissionIncrement = 0 Then
Calc = 1
Else
Calc = ((CommissionMax – CommissionBase) / CommissionIncrement) + 1
ScaleVar = 0
CommissionVar = 0
Build a table called tblScale with fields Scale and Commission
Loop 1 to Calc
ScaleLow = ScaleBase + ScaleVar
ScaleHigh = ScaleLow + ScaleIncrement – 0.1
Scale = ScaleLow & “ – “ & ScaleHigh
Commission = CommissionBase + CommissionVar
Populate tblScale with Scale and Commission
ScaleVar = ScaleVar + ScaleIncrement
CommissionVar = CommissionVar + CommissionIncrement
End Loop
Example of a result would look like this:
Scale Commission
100-104.9% 1%
105-109.9% 2%
110-114.9% 3%
Thanks in advance for any suggestions.
Rick