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
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
Last edited: