|That ERD I posted should work for that. The middle associative entity labeled "Employees-Departments" uses an employee_id (pulled from the Employees table) and a department_id (pulled from the departments table). Each "row" also has a date associated with it, and a indication of whether or not it's the "home" department.|
In the Employees table you would have something like this:
In the departments table you would have:
The associate identity (Employees-department) would end up containing data like this:
That indicates (going down the rows in order)
John Doe was certified to work on Line 1, which is his home department on 1/1/2009.
John Doe was certified to work on Line 2, which is not his home department, on 2/1/2009.
Jane Smith was certified to work on Line 2, here home department, on 1/1/2009.
Billy Bob was certified to work on Line1 (not home department) on 6/6/2009.
Billy Bob was certified to work on Line 3 (home department) on 6/6/2009.
I'm not very good at Access reports (which I assume is what you are using), but you should be able to generate all the reports you mentioned from that table setup. You can do things like return a list of all employees who are certified on Line 2, or you can return a list of all employees who are certified on Line 2 and where Line 2 is there home department. You could also do something like return a list of all employees who have a certification that expires with-in the next 3 months, and also show on the report whether or not that certification is in their home department.
Free Computer Tips and more:http://RyanTAdams.com
Paid Tech Support: Black Diamond