Help with calculate salary according KPI with incremental bonus value using query or any other way

[balboy]

Good Day ,

The Table :



1)
I want to calculate emp_salary and emp_total_salary based on emp_kpi(the value will be insert manually between certain ranges) , the emp_bonus will increment

if emp_kpi ranges between :-



0 <= 40 ; emp_bonus = 0.05

0 <= 69 ; emp_bonus = 0.08

0 <= 100 ; emp_bonus = 0.11

2)
the formula to calculate :-

emp_salary = emp_basic_pay + emp_allowance + emp_travel_cost

emp_total_bonus = emp_salary * emp_bonus

emp_total_salary = emp_salary + emp_total_bonus

3)



build expression here ?

4)
I've been trying googling and stuff since yesterday and unable to do this since this is my first time using microsoft access . I use expression builder with Iif to calculate but still got expression error

Thank you

[June7]

What is the exact error?

The Employee_Salary table is not a normalized structure. Summing data would be easier if each pay item was its own record.

Arithmetic with Null returns Null. If any field in the expression is Null the result of the addition will be Null. Have to handle possible Nulls.

Nz(emp_basic_pay,0) + Nz(emp_allowance,0) + Nz(emp_travel_cost,0)

Info about Null: http://allenbrowne.com/casu-12.html

Also review: http://allenbrowne.com/QueryPerfIssue.html

[CJ_London]

suggest look at the switch function

emp_total_bonus = emp_salary * switch(emp_kpi<=40,0.05,emp_kpi<=69,0.08,true,0.11 )

use true for the last condition if emp_kp can be greater than 100, otherwise you could use emp_kpi<=100

[balboy]

Thank you so much for both of your reply,it does help me a lot ! TYSM both of you