MS Access 2013 record history

[1021557]

Hi, I want to create a pay database for some employees. It will only need a few details one of which will be the department the employee is assigned to. However, when an employee moves from one department to another will I overwrite the department history such that if an employee moves form Department A to B will I lose the fact that they were in Dept A but now are in Dept B. In short how do I know where they are now and also importantly where they have been? Is their a setting when creating tables to do this or is their another way of keeping track of this?

 

[pocquet]

Hi,

Essentially you want to create a history of departmental changes based on the employees.

I would have 2 separate tables One for employee details and one for movement history.

The employee table with only hold 1 record of each employee based on a primary key (payroll number would be good) have a field called "current department" which contains there upto date department.

The History table will contain the employee number from the first, department name and 2 dates that represent the Start date in the department and the end date. If the employee is currently working in a department then you can leave the end date as null.

You can then join the two tables together in a query via the employee number and produce a output.

Depending on the scale you could manually keep this up to date or use an update query to update the employee table department name, where the history table end date is blank.

This way you can get a "where are they now" view and a "where have they been" view.

 

[Micron]

Or add a checkbox field (e.g. CurrentDept) to the table to flag which is current. Your date fields will record start/end dates.
If you want to ensure that for any employee id only one dept can be True, make EmplID and CurrentDept a combined unique index or a composite primary key. For the latter, some say composite PK's are not the way to go, but I've dealt with them for many years and don't know the reason for that sentiment.