Need help designing a simple Skills/Training Matrix

[RoyLittle0]

Hi, i am new to this forum by I have been using Access for some time now, I am producing a Service Logging System for my company and would like to add a simple skills matrix to it.

As with all companies the employees will change over time and so will the machines so these need to be in columns of my tables. the two Tables that I have are:-

tblEmployees


ID (pk)
FullName (at the moment 14 engineers)
Position

tblMachineType
ID (pk)
MachineType (aproximaterly 30 machine types)
SerialNumberRange

There will be 4 skill ranges for each machine "No Knowledge";"Limited Knowledge";"Useful Working Knowledge";"Totally Competent";"Training Competent"

How can I produce a junction table to link skills to the engineer for each machine?

[June7]

tblEmployessSkills
EmpID (FK)
MachID (FK)
SkillLevel

Then build crosstab query to get the 'matrix' with machines as columns.

[RoyLittle0]

Thanks June7

I have produced the new Table and query and all looks good

the problem i have now is the form i have produced from the query will not allow me to update or navigate, I can navigate through the navigation panel at the bottom of the form but not the combo box, the error that is showing is "This Recordset is Not Updatable" i assume this is because there isn't a Primary Key relating to the Crosstab Query, is there a way that i can add this to the query? i have tried a few things but none seem to work.

[June7]

Crosstab queries are by nature never updatable. They are intended to manipulate data for output, not facilitate input.

[RoyLittle0]

Thanks for the info, from the Tables i have is there a way to produce a Form for inputting the data for each engineer, so engineer on a combobox and each machine type on a text box

tblEmployees
ID (pk)
FullName (13 engineers)

tblMachineType
ID (pk)
MachineType (40 machine types)

tblEmployessSkills (this now has 520 records)
EmpID (FK)
MachID (FK)
SkillLevel

[June7]

You could just have a form bound to tblEmployeeSkills that has a combobox for employees and a combobox for machines and a combobox for skill level. Could set the 3 fields as a compound key to prevent duplication of combinations.

Or can do form/subform arrangement.

Main form bound to tblEmployees and subform bound to tblEmployeesSkills with comboboxes for MachID and SkillLevel

or

Main form bound to tblMachineType and subform bound to tblEmployeesSkills with comboboxes for EmpID and SkillLevel