Competency Matrix for Contacts (1 Viewer)

[bevc]

I have an already existing database of 500 odd contacts, and I need to set up 2 competency/skills matrices that can be completed for them.

For either matrix, there are 38 categories/skills, and there are 4 possible options that they can select (red, amber, green, and specialist).

I'm new to databases and my first instinct is just to set up a sub-form, but I have the phrase 'data normalization' stuck in my head, and I don't want to just go ahead and assume I know what I'm doing when I probably don't. As the four options that can be selected are uniform across all 38 (actually 76) categories/skills, is there a better way for me to do this? Ideally I want to be able to print reports of any of the different skills with a ranking of the contacts by the four categories.

I could use an excel spreadsheet, but the contacts already exist in Access, and I'd rather keep all the information in one place. If anybody could point out where I'm going to go wrong before I start, I'd be grateful.

 

[Uncle Gizmo]

I recently did a series of videos showing how to add a checklist to an MS Access database, then I added a further set of videos showing how to turn the original single checklist idea into multiple checklists, then I did a further set showing how to turn it into a questionnaire. I'm pretty sure you could change the checkbox into an option group however I would need to check myself before sticking my neck out too far! Have a look at the videos which are here:-

https://sites.google.com/site/msaccess457966vmfjg/advanced-techniques/check-lists

https://sites.google.com/site/msaccess457966vmfjg/advanced-techniques/questionnaires

and if you think it's something you could tackle I will look into changing the checkbox to an option group and let you know what I think before you waste too much of your own time.

 

[Uncle Gizmo]

I did a quick test and it appears to have converted OK into an option group display. However I had to use a continuous form instead of a datasheet, I don't think that would be a problem however I'm not 100% sure.

 

[JLCantara]

First, you never start wandering about forms when DB structure is not set. Attached, is a solution to your post.

You will need 2 forms: frmContacts, the main one, having a combo to select the contact.

The 2nd one, frmContactSkills, is a sub-form of the previous one (Contact is the link between them) and displays the contents specific to the contact.

Now here is the catch: no one will want to enter 38 records!!! So here is VBA...
In the AfterUpdate event of the combo, check if the skills are present; if not, CREATE all the records with Null as the Option. This way you solve the case of a new contact AND addition of new skills...