Best approach to store yes/no type information for many records (1 Viewer)

[diberlee]

Hi,

I'm working on a database to store details of current employees and I'm a little stumped for the best way to approach part of it.

For each person who works for our company, we would like to store details of which tasks they are trained to do. The list of tasks is quite long, possibly 70-80 different tasks initially. New tasks will probably be added fairly often. Most staff members will have been trained on 20-30 tasks.

The only way I can think of to do this is to have a separate table linked to my main staff table by staffID with a yes/No field for each task. However, that seems terribly inelegant and I wondered if anybody has a better suggestion?

Regards
Duane

 

[Frothingslosh]

You would want an employees table, a tasks table, and a 'training' table, for lack of a better name.

The employees table would have information specific to each employee - name, department, ID number, things like that.

The tasks table would just be an autonumber (TrainingID, say) as primary key, a task name, and perhaps task description, requirements, and anything else specific to that task.

The training table would include employee ID, TrainingID, and anything specific to this employee's training is this task (training date, certification date, expiration date, whatever). (This is what's called a junction table.)

Then any time an employee gets trained, you'd add an entry to the training table listing the employee ID, the ID of the task they were trained in, and any additional data you included in the training table like date of training.

Make sense?

 

[diberlee]

Yup, that makes perfect sense. 20 records with 3-4 fields each sounds a lot easier to keep up with/record than the option I was considering.

I would like to also include a way of saying that a person who has done a certain job role can inherently do certain tasks. I have a "Roles" table that stores RoleID and description. Could I just add a field that reads "2,3,5,6" to show that anyone starting that role can be recorded as being able to do tasks 2,3,5 and 6 where those numbers relate to a task in the "tasks" table? I guess then when I switch job roles elsewhere I can use VBA to just make those entries in the task table...

Funny it should be you who replied, you helped me a while back with the initial set up of this same project. I've been working on other stuff and recently come back to it... I'm not just really slow, in case you wondered why it's taking so long

Cheers

 

[Frothingslosh]

The way I'd probably do it is to add another table, maybe 'inclusions', and tie it to the tasks table. In the 'inclusions' table, you simply have a task ID to tie it to, and a field containing a single task ID that is included as part of the associated task.

So if you have tasks A through H, and someone being trained in task B can also do tasks C, D, and E, then this inclusions table would have three records. The first field (primary task) would be B in each category, and the second (included task) would be one of C, D, and E.

Then you put code into your form that checks for matches in the 'inclusions' table any time a new task is added to an employee's history, and if it finds any, it adds them as well.

There are all sorts of ways you can display this, too, so don't worry too much about the training table having tons of entries for each employee. Form/Subform is the one that comes immediately to mind.

And no, I hadn't been wondering. I often don't even read the name of the person posting, and, well, memory is the first thing to go, they say.

 

[diberlee]

That sounds good, definitely bit cleaner to implement/maintain than the way I was thinking of.

Thanks for your help on this one... Not that you'll remember

 

[Frothingslosh]

lol


10-char

 

[vbaInet]

Fyi, the model you're looking for is here:

http://www.databaseanswers.org/data_models/events_and_staff_training/index.htm