Training Database - One Employee to many tasks

[Harley Guy]

I have been trying to figure out for the last couple of days how to do this...and none of my attempts have gotten the results I desire.



Here's what I have:

I have 1 table with all of my employees
I have 1 table with all of the tasks listed in my shop
I have 1 table with training level, which shows the level of conmpetence an employee would have at each job.

My goal is to attache all tasks to an employee and show the training level for each job the person has been trained on.

For instance:

Bob Employee

Tasks Training Level
Installing Windows 1
Installing Door Seals 3
Installing Outside Mirrors 2
Installing Overhead Harness 4

So I would be able to build a query using employees clock number at it would show what task the person has been trained on and what level of competence that person has.

How can I relate each of these tables to show multiple tasks to one employee?

[jzwp11]

How about something like this:

tblEmployee
-pkEmpID primary key, autonumber
-ClockNo
-txtFname
-txtLname

tblTasks
-pkTaskID primary key, autonumber
-txtTaskname

tblEmployeeTasks
-pkEmpTaskID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-fkTaskID foreign key to tblTasks
-fkTrainingLevelID foreign key to tblTrainingLevel

tblTrainingLevels
-pkTrainingLevelID primary key, autonumber
-longLevel

[Harley Guy]

OK...I suppose I should have pointed out that I am a novice when it comes to Access...But I understand the concepts.

The Employee tables has their clock number as the primary key...in fact, in all of the tables, I have primary keys based on clock#, Task ID # and Traning Level#...I didn't see a need for the autonumber field.


But I will need an explanation of the foreing key...is that done when created relationships to each of the tables?

[jzwp11]

I have read many discussions on primary keys, but one thing that is typically said is that the primary key should have no significance to the user. It is your call on that, but I should caution that spaces and special characters in field and table names can cause some issues so instead of clock # and task ID #, I would recommend ClockNo and TaskIDNo (or something equivalent).

But I will need an explanation of the foreing key...is that done when created relationships to each of the tables?

Exactly, a foreign key field would hold a value that matches the value in the primary key field of another table, thus relating the two tables

So if you had this:

tblEmployee
pkEmpID|ClockNo|txtFname|txtLname
1|7654|Sherlock|Holmes
2|2323|John|Watson


tblTasks
pkTaskID|txtTaskname
1|Installing Windows
2|Installing Door Seals
3|Installing Outside Mirrors


tblEmployeeTask
pkEmpTaskID|fkEmpID|fktaskID
1|1|2 (sherlock holmes and installing door seals)
2|1|3 (sherlock holmes and installing outside mirrors)
3|2|1 (john watson and installing windows)

I did not put in the level but that would just be another field.

[Harley Guy]

OK, I have added a field for the primary key that has no signifigance to the user...I am trying to make this training database as bullet proof as I can.

But my question is this. I have 82 tasks that all employees need to be trained on. I need to show what training level for each task that a single employee is at.

John Watson
1. Installing Windows Level 2
2. Installing Door Seals Level 1
3. Installing Outside Mirrors Level 4

Once I can get that figured out, designing a form for user input/updates as well as creatign reports will be a snap. Just need to get over this hurdle.

Am I making sense? Cause if I am, I'm not to me!

[jzwp11]

For tblTrainingLevel, I had the level as a numeric field (i.e. just the number of the level). If you want to show the word Level in addition to the number, then you will need the Level field to be a text field.

I did not include the level in my earlier post but let's say that tblTrainingLevel looks like this

pkTrainingLevelID|txtLevel
1|Level 1
2|Level 2
3|Level 3

Now if we go back to tblEmployeeTask and include the foreign key to tblTrainingLevels

tblEmployeeTask
pkEmpTaskID|fkEmpID|fktaskID|fkTrainingLevelID
1|1|2|1 (sherlock holmes and installing door seals level 1)
2|1|3|2 (sherlock holmes and installing outside mirrors level 2)
3|2|1|1 (john watson and installing windows level 1)

In terms of displaying data the following can easily be done in a report or form
John Watson
1. Installing Windows Level 2
2. Installing Door Seals Level 1
3. Installing Outside Mirrors Level 4

For the form, you would have the employee info in the main form and the task information in a subform on that main form.

Now, if every employee gets the same 82 tasks, you can run an append query to append records in tblEmployeeTasks (82 records for each employee). You would then use a form to assign the appropriate level for each task for each employee. Now, if you have that information in a spreadsheet already, there are ways to import that data and put it into the table structure I have outlined (again using queries).

[Harley Guy]

I'm going to need to ponder this...the explanations just aren't quite synching in my head...I can see what I want, just need to figure out how to get there.

While I have all of this information in various forms (Excel, Word, Adobe), it's a mish-mash and not in any realy logical order. Which is part of the reason I wanted to get this into a database so that it can be expandable as well as much easier to use and all of the information is in one place.

I'm a quasi-IT guy working in a manufacturing production environment...their main goal has been to get product out the door. Processes, documentation and training are all taking a back seat but I am taking on the task myself to get them into the 20th century...

[jzwp11]

I decided to put together a sample database so that you can see how the design translates into a form (frmEmployee) and a report (rptSummary). The database is attached.

[Harley Guy]

This is great...And if you don't mind, I will use this...but I will study what has been done here so that I can troubleshoot and modify as needed.

Thank you very much JZ...you have saved me a ton of time and agony...but I will reverse engineer this so I can understand what was done.

Thanks again!

[jzwp11]

Feel free to use it & you're welcome. Please post back with any question you may have.