lookup values

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

 

[Michel Walsh]

Hi,



with the fields:


Salesman, comm, fromThis, toThis
ben 0.02 0 0.20
ben 0.03 0.20 0.50
....


then

DLookup( "comm", "tableNameHere", "Salesman='" & strWho & "' AND " &
margin & ">= fromThis AND " & margin & "< toThis" )


should return the appropriate commission, given the margin (from 0. 00 to
1.00) and who is the salesman, strWho.




Hoping it may help,
Vanderghast, Access MVP

 

[sam]

Thanks, I'll give it a go. It's not an easy one to set up
in a nice way. I was hoping to avoid duplicating the
salesman's name but the layout is the only one I could
think of as well.