2 Table to generate separate matrix table

[rmacnair]

This is my first post on the forum relating to my first foray into MS Access.

I will keep it simple because I am thinking there will be a simple answer.

I have 2 tables
- Staff Position(Unique Position Name, Description, Hierarchy)
- Training (ID, Name, Description etc.)

I essentially want a table with Staff Position as the Field, and Training as the Rows. The intersecting entries/matrix will be Yes/No to say whether that staff positions requires that training.

Simple Example

..................... Worker ........ Senior ........ Principal
IT Training ......YES ............. YES .............. YES
Accounts ......... NO ............. YES .............. YES
Management ... NO ............. NO ............... YES

I need the user to be able to add as many training entries and as many staff positions as they want. Please someone tell me this is relatively easy

It doesn't HAVE to be that sort of format, if someone else has a better way of doing it, I'm willing to try anything

 

[Cronk]

You need a third table, tblStaffTraining with fields
StaffTrainingID (autonumber)
StaffID (key field in tblStaff)
PositionID (key field in tblSkill)
Training (Yes/no to indicate whether the staff position needs the skill (I've called your "taining" a skill instead.)

A cross tab query can give you the output you require.

 

[rmacnair]

You need a third table, tblStaffTraining with fields
StaffTrainingID (autonumber)
StaffID (key field in tblStaff)
PositionID (key field in tblSkill)
Training (Yes/no to indicate whether the staff position needs the skill (I've called your "taining" a skill instead.)

A cross tab query can give you the output you require.

Thanks Cronk,

Took a little bit of understanding but got that sorted now. Didn't need Staff ID as it's only position and training I was interested in.

 

[Cronk]

I know you think you don't need StaffID but you may one day. When the Staff position title changes, you will then (if you remember) have to change it in two places (or more). And it might be someone else who changes it and can't figure out why the database is not working any more.

 

[rmacnair]

Thanks, will do