Using MS Access/Employee Training Database
QUESTION: I am creating a database in MS Access 2010 that tracks training for employees based on their job position. For example, every system administrator has several tasks, such as installing Windows Server 2008. Another example is a network administrator has several tasks, such as configuring a Cisco switch.
The tables that I currently have:
Job Positions //The job position assigned to an employee
Tasks //The training tasks assigned to a job position
Training //Records the training status, complete or not complete for an individual
I want to be able to assign tasks to a job position, then assign a job position to an employee, then check off either yes/no if they have completed that task.
I am having trouble making a Personnel form that shows all the tasks that are assigned and the status of those tasks.
ANSWER: Your Job Positions table should just list the positions. The tasks table should just list the tasks. These are a many to many relation. A job position can have multiple tasks and a task can be assigned to multiple positions. So you need a junction table to manage that relationship.
Then your training table would look like this:
TrainingID (PK Autonumber)
Hope this helps,
---------- FOLLOW-UP ----------
QUESTION: I would really like all the tasks for a job position to be "loaded" against an employee when I add the job position to that employee. For example, Bob is a new employee. In the Personnel table, as soon as I add System Administrator to the Bob's record for Job Position, I would like all the training tasks to be assigned to Bob for that job position. So I can run a report that won't show any network admin tasks for Bob because that's not his job and it will show that he hasn't completed training on any of the system administrator tasks yet.
OK, so you have you junction table, like I said.
PositionTaskID (PK Autonumber)
When you assign a position to an an employee, you can automatically populate tblTraining with a record for each PositionTaskID for that position. For example the code might look like this:
Dim strSQL As String
strSQL = "INSERT INTO tblTraining (PositionTaskID, EmployeeID) " & _
"SELECT PositionTaskID, " & Me.EmployeeID & " AS EmployeeID " & _
"FROM tjxPositionTask " & _
"WHERE PositionID = " & Me.PositionID & ";"
Your report would be based on a query that joins the tables on PositionID>
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA