Lookup

[sam]

Hi,
I'm just after some help on how I might go about setting
up the following to give me an answer. I have then
following rates for various salesmen. In the example, if
the sale was one of ben's and the margin on the job was
25% he would receive 3% commision. If the sale was bobs
he would receive 4%. I know who the salesman is and the
margin and am looking for ideas on how I could set this
up to give me the commission rate for the sale also
allowing for changes in the rates and additions to the
sales team. Any ideas?

Ben 2% 0%-20%
3% 21%-50%
4% 51%-80%
5% 81%-100%

Bob 3% 0%-15%
4% 16%-40%
5% 41%-70%
6% 71%-100%

 

[Howard Brody]

Since it looks like your sales people can have different
commission rates and different tiers, I would put them a
table (tblCommRates) with the fields Salesman, TierBottom
(the lowest margin percent for the tier), TierTop (the
highest margin percent for the tier) and CommPercent.

The data listed below would look like:

Ben 0 20 2
Ben 21 50 3
Ben 51 80 4
Ben 81 100 5
Bob 0 15 2
Bob 16 40 3
Bob 41 70 4
Bob 71 100 5

When you set this up in a form, use a ComboBox for the
Salesman (cboSalesman) and either another ComboBox or a
TextBox for the margin percent (txtMarginPercent) and a
TextBox for the commission percent(txtCommPercent). When
both are filled in, use a DLookUp to pull the appropriate
commission percent:

Dim strSalesman as String
Dim intMargin as Integer
Dim intCommPercent as Integer

strSalesman = [cboSalesman]
intMargin = [txtMarginPercent]
(or intMargin = ([txtMarginPercent]*100) if you're
entering the percent as a decimal instead of a whole
number).
intCommPercent = DLookUp
("[CommPercent]","tblCommRates","[Salesman]='" &
strSalesman & "' AND [TierBottom]<=" & intMargin & " AND
[TierTop]>=" & intMargin)

(if you're using the percent as a decimal instead of a
whole number, add:
Dim dblCommPercent as Double
dblCommPercent = (intCommPercent/100)
)

[txtCommPercent] = intCommpercent (or dblCommPercent)

Hope this helps!

Howard Brody