Overriding Result of a Calculated Field Depending On Other Fields (1 Viewer)

Hello all,

I hope somebody will be able to help with this (and that I explain it in a way that makes sense).

Back in August I was asked to design a form to allow my company's in-house quality assurance and coaching team to evaluate the level of service our customer service agents provide to customers and, thanks to the help of a few people on this forum, I was able to get it made. Now the quality team would like an update put in place to the form that changes the outcome of an evaluation based on more criteria.

The overall outcome is determined from the results of 8 assessed sections, each marked on a scale of Brand Damaging [BD] - Poor [P] - Fair [F] - Good [G] - Excellent [E] with points awarded for each (-2 for BD, -1 for P, 0 for F, 1 for G and 2 for E). Currently the score is weighted against the number of sections that are applicable and an overall outcome assigned with the below code (where Me.Max_Total counts how many sections are active for assessment and Me.Score_Sum is the overall score):


What I'm now being asked to do is add in conditions that will mean that:

+ 4 or more "Brand Damaging" results from the 8 sections will result in an overall "Brand Damaging" result all of the time.
+ 3 "Brand Damaging" results from the 8 sections will result in an overall "Poor" result but should only kick in if the calculated result from the combined score would conventionally return a "Fair", "Good" or "Great" score.
+ 2 "Brand Damaging" results from the 8 sections will result in an overall "Fair" result but should only kick in if the calculated result from the combined score would conventionally return a "Good" or "Great" score.

For example should an assessment return 3 "Brand Damaging" (-6 points) and 5 "Poor" (-5 points) our scoring should result in that being an overall "Brand Damaging" (scoring -11 points) without the condition above kicking in. Unfortunately try as I might I cannot figure out a way to get the code to function in such a way and it's applying in every instance even where it shouldn't, like with the 3 BD and 5 P example.

Any help anyone can offer would be very much appreciated.

Thank you in advance for your time and help!

if you start nesting IIFs like this ,then you need to change.
instead, use a lookup table.... Join this table into your data query, so it does the 'IF'.

the result of the score will produce the result from the lookup table in the query.

Ranman256 said:

if you start nesting IIFs like this ,then you need to change.
instead, use a lookup table.... Join this table into your data query, so it does the 'IF'.

the result of the score will produce the result from the lookup table in the query.

Click to expand...

Apologies but I'm practically a beginner when it comes to Access, I just have the unfortunate distinction of knowing just that little bit more than anybody else and ergo it's all fallen to me. I'm sure that were any of you to look at how this form all works you'd be appalled.

How would the lookup work in terms of then factoring in a certain threshold of one outcome and assigning that score?