Obtaining reports with old data and new data...

[MistyWolf]

I have a database that keeps information about a user and their tasks performed for a manufacturing outfit.

user table:
Primary Key
Employee Code
Employee Name
Title
Dept
Password
Security Level
LockedOut
Present

tasks table:
Primary Key
Assembly number
Work Order
Part Number
Part Description
Location
StartDate
StartTime
EndDate
EndTime
TotalTime
Quantity


My problem is that employees can be moved from one department to another. This is a problem because management wants to go back to the beginning of time to obtain efficiency reporting on the employees and departments. The way this database is currently set up the department is in the employee table. Where can I put the department information so that as departments for employees change, I can still retain the old department information for reporting purposes? Or is this a feasible practice to do?

Hopefully I explained this correctly. Let me know if this does not make sense and I will try again.

MistyWolf

 

[AlexMidd]

This would then be a many-to-many relationship between employees and departments, so you would need to set up a departments table and an in-between table, employees-departments, which would have EmployeeID and DepartmentID as a composite primary key and, possibly, other data such as the dates between which they were in that department, and the tasks table would have a link to that table as well. Then you could report for an employee and include historical data for all the departments they have worked for.

Have fun! )

Alex Middleton