2 Variable Lookup

[ggrinham]

I am building a commission db. I am trying to write query that returns the
payout percentage given 2 variables; revenue and pullthrough percentage.
Here is a sample payout table:

Revenue Pullthru Payout%
15,000 3.00% 10%
15,000 5.00% 12%
15,000 7.00% 15%
40,000 3.00% 13%
40,000 5.00% 16%
40,000 7.00% 18%

For example, if a sales rep has 20,000 in revenue and 6.00% pullthrough, the
payout percent would be 12%. Any help with this is much appreciated.

 

[mscertified]

I cannot figure out based on your examples what the calculation is but in
your query just use an arithmetic expression e.e.
SELECT (Revenue * PullthruPct) as PayoutPct FROM Mytable

substitiute your own expression
bear in mind that percentages are stored as decimal fractions e.g 10% will
be .1

Dorian

 

[Michel Walsh]

Hi,


DMin("Payout", "tableName", "Revenue<=" & whichRevenu & " AND PullThru<=" &
whichPullThru)



That works only as long as the payout increases as long as revenue and
pullthru increases. For the numerical numbers you ask (whichRevenue=20000
and whichPullThru =0.06) the answer should be 0.12



Hoping it may help,
Vanderghast, Access MVP

 

[ggrinham via AccessMonster.com]

Thank you very much for the help, I will try this.

GG

Hi,

DMin("Payout", "tableName", "Revenue<=" & whichRevenu & " AND PullThru<=" &
whichPullThru)

That works only as long as the payout increases as long as revenue and
pullthru increases. For the numerical numbers you ask (whichRevenue=20000
and whichPullThru =0.06) the answer should be 0.12

Hoping it may help,
Vanderghast, Access MVP

I am building a commission db. I am trying to write query that returns the
payout percentage given 2 variables; revenue and pullthrough percentage.

[quoted text clipped - 11 lines]

the
payout percent would be 12%. Any help with this is much appreciated.