Create Query using Relative "VLookup" type function
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.
Payouts
Plan Attainment payout
Plan_1 0 0
Plan_1 100 5.4
Plan_1 110 5.7
Plan_1 120 6
Plan_2 ...
Sales_Results
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! ;-)
Payouts
Plan Attainment payout
Plan_1 0 0
Plan_1 100 5.4
Plan_1 110 5.7
Plan_1 120 6
Plan_2 ...
Sales_Results
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! ;-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this:
Select a.Emp, a.Plan, a.Quota, a.Results, a.Attainment, Select Min(b.Payout) from Payouts b where b.Attainment>a.Attainment and b.Plan = a.Plan) from Sales_Results a Order By a.Emp;
Select a.Emp, a.Plan, a.Quota, a.Results, a.Attainment, Select Min(b.Payout) from Payouts b where b.Attainment>a.Attainment and b.Plan = a.Plan) from Sales_Results a Order By a.Emp;
Mighty fast on the trigger! My solution does require you to change anything.
ASKER