asked on

Calculated field in a query

Hello

I need some help making a query I have attached my sample database to this so that it can help.

I need the query to show the Code, Filed5 and a new field called TotalScore
Here is the part I need help on... it's the Totalscore. I need to make this a calculated field

the rules to this calculated field are if "Items Per user" = value / 24 so 24/24 =100 if value is 23 then its 23/24 = .95833333 but need the value rounded up or down accordingly so in this case.. multiply the .95833333 x 100 to get a score of 95.8333333 then need to round up or down... so this would = 96
also.. another kicker here... if the "Items per User" is over 24 then I want to give more extra points. IE if the "Items per User" is 25 then I want the TotalScore to be 100 + any points over the 24 .. in this case its 1 point over 24.. (25-24 = x) so the score would be x + 100 = 101
Do this extra points till it reaches the max of 115 total points. Then.. I want it to just equal 115 .. so once "Items per user" = 39 or greater then Totalscore is automatically = 115 no calculation need.

I hope you can help me with this.. I know this is a lot of steps.. and they why I need someone's expert help

Thanks

faithdanSample15.accdbHello

I need some help making a query I have attached my sample database to this so that it can help.

I need the query to show the Code, Filed5 and a new field called TotalScore
Here is the part I need help on... it's the Totalscore. I need to make this a calculated field

the rules to this calculated field are if "Items Per user" = value / 24 so 24/24 =100 if value is 23 then its 23/24 = .95833333 but need the value rounded up or down accordingly so in this case.. multiply the .95833333 x 100 to get a score of 95.8333333 then need to round up or down... so this would = 96
also.. another kicker here... if the "Items per User" is over 24 then I want to give more extra points. IE if the "Items per User" is 25 then I want the TotalScore to be 100 + any points over the 24 .. in this case its 1 point over 24.. (25-24 = x) so the score would be x + 100 = 101
Do this extra points till it reaches the max of 115 total points. Then.. I want it to just equal 115 .. so once "Items per user" = 39 or greater then Totalscore is automatically = 115 no calculation need.

I hope you can help me with this.. I know this is a lot of steps.. and they why I need someone's expert help

Thanks

faithdan

FaithDan

ASKER

Im close...

but this does not work.. any ideas?

query

totalpoints: IIf([Items per user]>39,[totalpoints]=115,[totalpoints]=Round(([Items per user]/24)*100,0))

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FaithDan

ASKER

I have this working... but it needs one more iif statement to stop it from going over 115... ie.. need another IIF > 39

totalpoints: IIf([Items per user]>24,([Items per user]-24)+100,Round(([Items per user]/24)*100,0))

FaithDan

ASKER

This is it... this does what I need

totalpoints3: IIf([Items per user]>24,IIf([Items per user]>39,115,([Items per user]-24)+100),Round(([Items per user]/24)*100,0))

Thanks for your help capricorn1

FaithDan

ASKER

thanks