# 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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Commented:
try this

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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.