Comparative Report/Query

[stevenb82]

Hi,

Can anyone tell me if this is possible in Access 2010...

I have a table of employees, who are required to complete a certain level of training for their role, depending on the department that they work in. So for example, if an employee works in Finance, they must complete Outlook and Excel training.. Simple enough.

I have set up my database as follows.

Tables: Employee, TrainingCourse, CompletedTraining.

The employee and trainingCourse tables are related through the completed training table. Something similar to how a customer, product and orders tables might be related. Then there is a lookup field in the employee table that looks up a list of departments.

In my form for employee details, there is a subform which records the training history as an employee completes each training course.

Is there a way to compare the training history of an employee against a training required table for their department so that I can report what training needs to be done..

If that makes sense?

Hopefully one of you whizzes will know what I mean and how to do it..

Thanks in advance!

 

[recyan]

Till some one comes along, a few thoughts :
tblEmployees
EmployeeID - PK
.......

tblDepartments
DepartmentID - PK
DepartmentName
....

tblEmployeeDepartments
EmployeeDepartmentID - PK
EmployeeID - FK
DepartmentID - FK
DateOfJoiningDepartment

tblTrainingCourses
TrainingCourseID - PK
TrainingCourse
....

tblDepartmentTrainingCourses
DepartmentTrainingCoursesID - PK
DepartmentID - FK
TrainingCourseID - FK
..........

tblEmployeeDepartmentTrainingCoursesUnderGone
EmployeeDepartmentTrainingCoursesUnderGone - PK
EmployeeID - FK
DepartmentTrainingCoursesID - FK
DateOfCourseCompletion
.....

Note :
1) Not considered difference between Training Requirements for Department & Role.
2) If for different Roles within the same Department, TrainingCourses to be undergone are different, then modifications need to be looked in to. IMO, this is important & should be looked in to before proceeding further.

3) Should be possible to compare what all Training Courses are pending for an Employee.

Have I missed the question ?

Thanks