2 Variable Lookup

  • Thread starter Thread starter ggrinham
  • Start date Start date
G

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.
 
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
 
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
 
Thank you very much for the help, I will try this.

GG

Michel said:
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.
Click to expand...
[quoted text clipped - 11 lines]
the
payout percent would be 12%. Any help with this is much appreciated.
Click to expand...
Click to expand...
 
Back
Top