A modelling Puzzle

[music_al]

Hi
I am trying to capture a team member’s performance against particular attributes, let’s say they are Speed, Strength and Agility – each scored from 1 to 10.
The baseline expected figure for each team member is dependent on their Role, so I have modelled this as shown in the attachment.
The baseline expected scores are recorded in three intermediate/junction tables as shown in the first attachment.
· Role_Speed
· Role_Strength
· Role_Agility
It’s important to know that not all team members will be scored on the same attributes.
Every 6 months we will perform an assessment of a team member and record this in a table that is date stamped. The table should show:
· the minimum expected baseline scores
· the team members self assessed score
· the team manager’s score
· all of these scores from the last assessment so we can determine if their performance against any of the attributes has improved, stayed the same or worsened
The challenge I have is that I don’t know how to show query data (the minimum expected scores) alongside data that is to be entered manually during each assessment. See the second attachment.
Any help would be appreciated.

 

[plog]

The baseline expected scores are recorded in three intermediate/junction tables as shown in the first attachment.

That's incorrect. Honestly, you could use 1 table to store both your baseline and your actual players' data. That table would look like this:

tbl_AttributeScores
as_ID, autonumber, primary key
ID_Player, number, foreign key to player table's id
as_Attribute, text, will hold the attribute you are scoring (e.g. Speed, Strength, etc)
as_Score, number, will hold the score of the attribute for this player

That is all you need for attributes. In your player table you would then set up "dummy" records for your baseline--one for each position. You would then put data into tbl_AttributeScores for those players and that would be your baseline. Next you would complete tbl_AttributeScores for your actual players.

For your reports you would use cross-tab queries (https://support.office.com/en-us/ar...ab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8). First you would make one for your baseline, then you would create another for actual players and JOIN your baseline query to those.

 

[MajP]

The baseline expected scores are recorded in three intermediate/junction tables as shown in the first attachment.
· Role_Speed
· Role_Strength
· Role_Agility

You will have to provide some details on your different tables. I cannot figure out why you would need three tables instead of just one. If you do it entity based then it would be

RoleID
MetricName
MetricValue

Striker Speed 8
Stirker Strength 6
Striker Agility 7

If not it is
RoleID
StrengthValue
SpeedValue
AgilityValue

 

[music_al]

I think I’ve maybe not explained myself clearly.
The baseline expected metrics are set by a central team and these are not captured by the person performing the assessment.
When a persons role is entered into a form via a combo box, a sub form should be populated with all the attributes that that person should be assessed on. The BASELINE scores should be per-populated from 3 different tables. There is a reason they have to come from different tables. You have to trust me on that.

I’ll add some more screenshots tomorrow.

 

[music_al]

OK, everyone is measured on 3 types of metrics...

Technical Skills
Professional Qualifications
Behaviours

All 3 are quite different.
Technical Skills are ranked as
Unaware
Aware
Familiar
Proficient
Expert

Behaviours (or the demonstration of them) are measured as
Self
Others
Organisation

And qualifications are just measured as a Yes/No - the person either has it or they dont.

However, different roles and different grades of each role are expected to have different skills, behaviours and qualifications, there is no standard. So it was necessary to have a junction table for each...

Role - Grade - Technical Skill - Skill Level
Role - Grade - Behaviour - Behaviour Level
Role - Qualification (this ignores the grade of the person)

So, when we select a Role and a Grade in a form when doing an assessment for a Project Manager, he/she might have

Qualifications needed
Prince 2 Practitioner

Behaviours
Considering our impact on the team - OTHERS
Considering the needs of the business - ORGANISATION

Technical Skills
Database Design - Familiar
JAVA - Proficient
MySQL - Aware

etc

If a Developer at Grade 6 is entered, he/she would have a whole different set of qualifications, skills and behaviours which are very different from a Project Manager and maybe slightly different than a Developer of a different grade.

So in doing an assessment, when these baseline metrics appear in the 3 sub tables when the role and grade have been entered, I would like two empty columns (fields) to appear next to each metric where the person being assessed can enter the score that they think they are at (its just a Yes/No for qualification) and a second column where the individual's line manager enters their score.

As I'm using a query to pull back the baseline/expected metrics, I don't know how to get the 2 blank fields in the same table.