I am programming a bonus calculator and have one table that lists payouts based on ranges of attainment scores. The other table calculates the attainment percentage. What I need is a way to pull the payout index into the "Sales_results" table (or a new make-table). I have tried a DLookup, Max combination but was not able to figure out the syntax. In Excel, this would be a simple VLookup using a TRUE to make it relative, but I don't know how to do it in access.
Plan Attainment payout
Plan_1 0 0
Plan_1 100 5.4
Plan_1 110 5.7
Plan_1 120 6
Empee Plan Quota Results Attainment These should result in the following payouts
1 Plan_1 100,000 105,000 105 5.4
2 Plan_1 125,000 100,000 0 0
3 Plan_1 100,000 120,000 120 6
4 Plan_2 ....
I look forward to seeing your brilliant solutiions! ;-)