Tracking education hours for employees

[sephiroth2906]

Hello all!

I have an employee database with all of the basic employee information you would expect. Each employee is required to complete 8 hours of education per year, which can come in all sorts of kinds (dvd, classes, etc.)



I am a little lost on how to proceed here. What I want to be able to do is enter hours and a description into a form for each employee and be able to query a department, supervisor, those under 8 hours, etc. I know how to build the query, but I don't know how to build the table it will draw from.

I can figure out how to make a separate table for each employee and tie it into the master, but I think there should be a better way to accomplish this with one table, but I cannot figure it out for the life of me.

I appreciate the help!

[jzwp11]

You would not have a table for each employee; doing so would violate normalization rules which will make your job of querying even more difficult. Since an employee can have many training items, that describes a one-to-many relationship which by normalization rules, requires a separate but related table to the employee table. That table structure might look like this (all training would go in this table):

tblEmployeeTraining
-pkEmpTrainID primary key, autonumber
-fkEmpID foreign key to employee table (identifies the employee taking the training)
-dteTraining (date for when the training occurred)
-txtTrainingDesc (a text field to hold the description of the training)
-fkInstitutionID foreign key to a table that holds a list of all institutions that offer training

With your present table structure, how are you handling the departments, supervisor for a department and people within a department?

[sephiroth2906]

Likely not the way I should be
Everything is on one table. The reason for that is some supervisors are over more than one department (not so hard) and the supervisors belong to a certain department but report to someone else (harder)

I also have to date been unsuccessful in creating a one to many relationship, mostly because I have only attempted it in theory and have never needed one until now. This is definitely my most ambitious database.

In this instance, I would want to build a query for each supervisor detailing what hours their employee has accrued and how many are left.

The table that I would like to create would have the employee name, supervisor, department (those I am sure would come into the one to many) date of education, title of class/dvd and hours given.

I do feel like a bit of a tool because I am essentially asking to have my hand held building this thing, but I am eager to learn and generally don't need to be told twice. Thanks so much for the help.

[jzwp11]

Setting up the table stucture correctly is the key to any successful relational database, so it is worth the time and effort to do it correctly. From what you have said, you do have some one-to-many relationships.

First, you will need your employee table that holds all employees including supervisors. You will also need a department table that holds a list of all departments. Something along these lines:

tblEmployees
-pkEmpID primary key, autonumber
-txtFName
-txtLName

tblDepartments
-pkDeptID primary key, autonumber
-txtDeptName



Since a department can consist of many people, you have a one-to-many relationship. But you also said:

...that is some supervisors are over more than one department

Since a supervisor is also a person and can head up more than one department, you have another one(supervisor/person)-to-many (departments) relationship. When you have two one-to-many relationships between the same two entities (people and departments) you have a many-to-many relationship for which you need a junction table. All foreign key fields are long integer number datatypes to match the datatype of the autonumber datatype of the corresponding primary key field.

tblDepartmentEmployees
-pkDeptEmpID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-fkDeptID foreign key to tblDepartment
-dteEffective (effective date of when the employee joined the department) you will need this if you intend to track movement of employees between departments

Now you could also add a field to determine the role or job title the person plays in the department-- i.e. manager. If you have set roles, I would recommend having a table that lists all roles (as records) and then reference the key field in the above table.

tblRoles
-pkRoleID primary key, autonumber
-txtRoleName

Now reference the roleID in the tblDepartmentEmployees

tblDepartmentEmployees
-pkDeptEmpID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-fkDeptID foreign key to tblDepartment
-dteEffective
-fkRoleID foreign key to tblRoles

As to how we structure who the manager reports to depends somewhat on your business. If the upper level managers can be grouped in a "department" then you can include the managers within that department. Otherwise, you may need a separate structure for defining the managment hierarchy; perhaps something like this

tblManagement
-pkManID primary key, autonumber
-fkEEmpID foreign key to tblEmployees (represents the executive)
-fkMEmpID foreign key to tblEmployees (represents a person/manager reporting to the indicated executive