Developing a Decision Matrix

[twinnyfo]

Hi, Friends,

I've been away for a while doing some other things, but I'm back with a tricky question.

I'm working on an employee development database that uses surveys to assess an Employee's competence in several functional areas of expertise. The surveys compile data from both the Employee and the Employee's Supervisor, on a scale from 1-5, with 5 representing a high level of competence and 1 representing a very low level of competence. Based on the results of these surveys, I need the Database to go through some form of "Decision Matrix" to develop an individualized training plan for the Employee. Please take a look at the attached file to see what I am talking about (sorry--I could only get it to upload as a zip file). The attached matrix is for demonstration purposes only, so ignore any values that are actually in it; my concern is how to translate this matrix into the Database.

As you can see, the training recommendation will change based on both the Employee's self-assessment and the Supervisor's assessment. This is the challenge, because as you can see, there are a total of 25 different possibilities for survey results. However, depending on the options we choose for training recommendations, there could be a total of eight training recommendations for each of those 25 possible survey results. Also keep in mind, that we are assessing 35 Competency Areas, but the key is mastering the decision matrix first, then applying that logic to each competency area.

My initial thought was to use some type of array within the code. But, this decision matrix needs to be accessible by the administrators to be able to customize the training recommendations based on the survey results (we might even want to have a different matrix for each competency--which addes complexity, but again, the key is coming up with a single solution that can be applied in multiple circumstances).

My second thought was to have the Decision Matrix reside in a Table, where we capture all of the possible survey results, then have Yes/No fields for each of the possible training recommendations. The administrators would then have access to the matrix and be able to set the training requirements as desired and the DB would incorporate any changes made. A partial table follows below to demonstrate:

Key  Supvr  Emply  Formal  Mentor  OJT
===  =====  =====  ======  ======  ===
 1     1      1       Y       Y     N
 2     1      2       Y       Y     N
 3     1      3       Y       N     N
 4     1      4       N       Y     N
 5     1      5       N       Y     Y
 
.....
 
 21    5      1       N       Y     Y
 22    5      2       N       N     Y
 23    5      3       N       N     N
 24    5      4       N       N     N
 25    5      5       N       N     N

In order to use the matrix, the DB would access the table through code in a recordset, and based on the survey results, determine which record in the table applied, then, based on the results of the Yes/No fields in that record, it would use these values to generate the individualized training plan.

I'm just wondering if anyone has ever come across any type of problem like this in their DB development and what type of solution they arrived at.

I want to emphasize that my question pertains specifically with the decision matrix itself. Even if I went with the table design described above, I think I can figure out the training plan development, but that type of constructoin, to me, seems a bit overly complex (at the worst), or clumsy (at its best). Additionally, this particular Table format would be more of a list, rather than a matrix (as the attached image is), and does not lend itself to ease of use--although I admit that this could be a visual paradigm that I am stuck on, rather than a functional one.

Any suggestions for incorporating this type of decision matrix?

I am open to any thoughts, ideas, questions, comments or words of wit!

Thanks for your help!

[nico5038]

The martrix basically describes the sum of the employee and supervisor values like:
D >= 8
C = 7
B,C=6
B=5
A=4
A,B<=3
So all you need to do is to sum the values to get the advise as described in the above table.

Getting the idea ?

Nic;o)

[twinnyfo]

Nic,

So, I think what you are saying is to not view the various types of training as individual entities, but view each particular permutation of training as a separate entity. In theory, then, the recommended training could be as simple as a dropdown box, or even an option group on the form.

Then, in the code, it evaluates the training requirement and responds appropriately. This might have some potential for working through this. let me play around with it a little bit and let you know if I come across any snags.

Thanks for the suggestion!

Greg

[nico5038]

I just placed the sum of the X and Y axis values in the grid and you'll see that D is represented by the values 8, 9 and 10,
C by the value 7, etc.

Thus having the score of the employee and supervisor will define the training advise for the registered functional area of expertise.
For multiple functional area's you could find the Min, Average or Max value of the proposed training to get a general overview.

Nic;o)