Need solution resolve changes history

[Cass]

Hi!

Is any good idea how to resolve changes history.
Example: Worker works today department X, next week department Y .... s.o

how to reflect the history worker movements between departments

first idea is when change the department field (combo box) then come up pop-up and ask dates when was the change and record it history table
now asking why pop-up - because the departments change was maybe yesterday or something like that.

Or is any better idea

[Cosmos75]

It sounds like you are storing the department in the employee table.

I suggest the follownig three tables.

tblEmployee
EmployeeID (Primary Key)
strFirstName - Employee's first name
strLastName - Employee's last name

tblDepartment
DepartmentID (Primary Key)
strDeptName - Name of department

tblEmployeeDept
EmployeeID (Foreign Key)
DepartmentID (Foreign Key)
dtStart - date emlployee starts working for a department
Note: Be sure that you create a unique multi-field index for all three fields in tblEmployeDept so that you can't have an employee working in two departments at the same time. Unless of course, that does happen in your case.

Just keep adding records to tblEmployeeDept. I'd create a Main form for either by Employee or Department (or one for both), and have a Subform with the details from tblEmployeeDept.

I hope I understood what you were looking for. Hope it helps!