Count Specific Words in a Record and apply a score

[alanma]

Hi,

I'm in the throes of putting a database together so that my employer can measure staff against certain competencies (or criteria).
I'm happy with the way the database is laid out, the tables, and the underlying relationships, but I can't workout how to score the the competencies.
Basically, for each record, there are nine competencies (At this stage!) that are being assessed, the idea being that we can assist staff who are struggling with those competencies. Of course, we can only do this by scoring how they're doing over a period of time which is where this database comes in.
Each criteria to be met, has a text "score" labelled as : "Correct"; "Partially-Correct"; "InCorrect"; and "Not-Applicable". "Not-Applicable" is scored the same as "Correct" since it is deemed so by default, but I still need to be able to track it.
I want to able to score each Record, based on the number of "Correct"; "Partially-Correct".... etc, answers.
Each "Correct" or "Not-Applicable" answer will have a loading of roughly 11% scoring against the 9 criteria, "Partially-Correct" will be worth around 5.5%, and "InCorrect" will score 0%.
Then I need a text representation of the total scores, such that 90-100% will score as "High Compliant"; 80-90% will score as "Compliant"; 70-80% "Partially-Compliant"; and below 70% "Non-Compliant".

I've not been able to find exactly what I want on google (Not for lack of trying!), so I'm hoping someone can help me out.
I'm assuming! that this will need some sort of Count Iif or Sum Iif statement, probably in a query? but that's about as far as I've got, and I can't quite get it over the line..

FWIW I'm running Access 2007, but the database will eventually be migrated to Access 2010 when it's uploaded to my employer's system.

Thanks in advance!

Regards,
Alan

 

[Micron]

Sounds like you need a Totals query that counts the number of values. Since you're looking to assign a rating based on a range (e.g.90-100) I think you'll also need a function with a Select Case block. This piece of code can assign a rating for any count value, something like

Select Case TotalScore
  Case Is 80 To 90
     myRating = "High Compliant"
  Case Is ...
    more ratings...
End Select

However, I find it virtually impossible to be specific because not enough of your design, process and desired outcomes is revealed in your post. Something would have to call the code that assigns the rating, then put the value(s) somewhere.

 

[alanma]

Hi Micron,

Thank you very much for that - appreciate your comments to, re lack of information in the post.
The Code you've given me, looks really good, and I'm tempted to use it some other upcoming projects.

On this occasion though, I've managed to finally work out what I wanted by building Queries to get what I wanted then using the "Switch" function in a field within the query to convert the results into a meaning-full score, and then an "Iif" statement in another field to convert the score to the text message result I wanted.. Might be a bit roundabout but it works!

So all sorted for now! Thanks again for your help.

Regards,
Alan