# 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
LVL 1
###### Who is Participating?

Commented:
try this

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

Author Commented:
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))
0

Author Commented:
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))
0

Author Commented:
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))

0

Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.