Using a Lookup Table

[Guest]

I apologize if I've used the wrong section.

I am working on a query that will calculate commissions for my salespeople.
Instead of creating a long formula in the query to calculate the result (the
percentage of commission each salesperson earns is directly tied to the
amount of profit each salesperson generates; therefore the commission rate
can change), I would like to have a table populated with different commission
rates that will be used in a shorter formula in my query.

Example: If a salesperson generates over 30% profit, he will earn 10% of the
profit; if he generates over 20% profit, he will earn 7.5% profit; if he
generates over 10% profit, he will earn 5% profit.

Can I have a formula in my query that will calculate the query field
"Profit_Percent" and then find the appropriate commission percentage in the
table? Can this be performed if a salesperson's "Profit_Percent" is a number
that is not in the table, such as 23.4%?

If this can be done, can someone show me the way this would be written so
that I can understand the thought process.

If I have not been clear in my rambling here, please ask me to be clearer in
my explanation; I've got to figure this out. Thanks...

 

[Guest]

I think that a Case statement would be best. Put the following in a module
and compile it.

Function fCommission(nCommission As Variant) As Variant
Dim TheCommission As Variant
Select Case nCommission
Case Is < 10
TheCommission = 0
Case 10 To 19.999
TheCommission = 5
Case 20 To 29.999
TheCommission = 7.5
Case Is >= 30
TheCommission = 10
Case Else ' Other values.
TheCommission = 0
End Select
fCommission = TheCommission
End Function

In a query you'd put something like below in a field:

Commission: fCommission([Profit_Percent])

Now as you are dealing with percentages, there might be a problem with the
number divided by 100 to get the percent. If so, you may have to modify the
case statement to something like Case Is < .01 and so on to get it to work
right.

 

[Guest]

I can do this; however, I wanted to avoid using code because I, or someone
else, may have to change the commission rates and that person may not
understand coding language. I was hoping to create a form to change the
values in the table.
--
Don Rountree

I think that a Case statement would be best. Put the following in a module
and compile it.

Function fCommission(nCommission As Variant) As Variant
Dim TheCommission As Variant
Select Case nCommission
Case Is < 10
TheCommission = 0
Case 10 To 19.999
TheCommission = 5
Case 20 To 29.999
TheCommission = 7.5
Case Is >= 30
TheCommission = 10
Case Else ' Other values.
TheCommission = 0
End Select
fCommission = TheCommission
End Function

In a query you'd put something like below in a field:

Commission: fCommission([Profit_Percent])

Now as you are dealing with percentages, there might be a problem with the
number divided by 100 to get the percent. If so, you may have to modify the
case statement to something like Case Is < .01 and so on to get it to work
right.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I apologize if I've used the wrong section.

I am working on a query that will calculate commissions for my salespeople.
Instead of creating a long formula in the query to calculate the result (the
percentage of commission each salesperson earns is directly tied to the
amount of profit each salesperson generates; therefore the commission rate
can change), I would like to have a table populated with different commission
rates that will be used in a shorter formula in my query.

Example: If a salesperson generates over 30% profit, he will earn 10% of the
profit; if he generates over 20% profit, he will earn 7.5% profit; if he
generates over 10% profit, he will earn 5% profit.

Can I have a formula in my query that will calculate the query field
"Profit_Percent" and then find the appropriate commission percentage in the
table? Can this be performed if a salesperson's "Profit_Percent" is a number
that is not in the table, such as 23.4%?

If this can be done, can someone show me the way this would be written so
that I can understand the thought process.

If I have not been clear in my rambling here, please ask me to be clearer in
my explanation; I've got to figure this out. Thanks...

 

[Guest]

I think a table driven approach is a good idea since commissions are changed
on a regular basis, mostly to keep sales people from making too much money

Since percentages will not always be an even amount, you can still use the
Select Case statement Jerry suggested with a small modification.

What I don't see in your post is how the profit is calculated. "calculate
the query field "Profit_Percent""

Tell me the rule on that and I can give you a better idea.


--
Dave Hargis, Microsoft Access MVP

I can do this; however, I wanted to avoid using code because I, or someone
else, may have to change the commission rates and that person may not
understand coding language. I was hoping to create a form to change the
values in the table.
--
Don Rountree

I think that a Case statement would be best. Put the following in a module
and compile it.

Function fCommission(nCommission As Variant) As Variant
Dim TheCommission As Variant
Select Case nCommission
Case Is < 10
TheCommission = 0
Case 10 To 19.999
TheCommission = 5
Case 20 To 29.999
TheCommission = 7.5
Case Is >= 30
TheCommission = 10
Case Else ' Other values.
TheCommission = 0
End Select
fCommission = TheCommission
End Function

In a query you'd put something like below in a field:

Commission: fCommission([Profit_Percent])

Now as you are dealing with percentages, there might be a problem with the
number divided by 100 to get the percent. If so, you may have to modify the
case statement to something like Case Is < .01 and so on to get it to work
right.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I apologize if I've used the wrong section.

I am working on a query that will calculate commissions for my salespeople.
Instead of creating a long formula in the query to calculate the result (the
percentage of commission each salesperson earns is directly tied to the
amount of profit each salesperson generates; therefore the commission rate
can change), I would like to have a table populated with different commission
rates that will be used in a shorter formula in my query.

Example: If a salesperson generates over 30% profit, he will earn 10% of the
profit; if he generates over 20% profit, he will earn 7.5% profit; if he
generates over 10% profit, he will earn 5% profit.

Can I have a formula in my query that will calculate the query field
"Profit_Percent" and then find the appropriate commission percentage in the
table? Can this be performed if a salesperson's "Profit_Percent" is a number
that is not in the table, such as 23.4%?

If this can be done, can someone show me the way this would be written so
that I can understand the thought process.

If I have not been clear in my rambling here, please ask me to be clearer in
my explanation; I've got to figure this out. Thanks...