MS Access tracking effective dates of employees

I currently have an MS Access application that stores information about the employees who have responsibility for a certain task.

My form goes like this. You enter the task in a textbox. You pick the employee from a combo box which is bound to the staff table. And finally we save this information to the Task table. As simple as that I tho开发者_JAVA技巧ught...

But here is the problem. No employee works forever in a company. A new/another employee maybe assigned the task which was previously carried out by an employee who is no longer working there. In the form once I update the new employee the old employee information is replaced and we wouldn't even know that that employee existed.

I came across the concept of adding effective dates to the employee which may be used to track the history of employees.

Now I would like to know how I would be able to preserve the data of the previous employee when I update the Task form with the information of the new employee.

What should I do?

Thank you for your assistance.

BR, Paul

I have uploaded links to three forms that I am working on.

• In the outside company officer form we don't have the date fields mentioned.
• In our companies proposal form we might need to add additional officers but don't want to repeat the proposal information just the officer, division, alternate officer & division.
• In the outside company detail form we might need to apply the same concept to the director of the company without applying to other institute information.

I was also researching on this topic where I found a document which explains the concept of effective dates. I need to know how to apply this and also without making a lot of changes to the application.

• www.gsa.gov/graphics/staffoffices/DatedInformationandDateTracking.pdf

I hope this helps you understand my problem.

It would seem you are keeping very simple information, you want to know who is currently assigned to any given task. Add a "assignedDate" field to your table. Then you can change any reports or forms where you need to display the current assignee to a query whereby you group by "task" and select only the latest record.

Do not include the assignee as a field in the task table. You would need an additional table, say "TaskAssignees" comprised of TaskID, ContactID, entry date, assigned date, unassigned date. Now in reporting you can pull the correct assignee given any date or list a history of the assignees from start to finish.

I would say that you are making the issue too complicated.

Surely there's an administrative policy on what happens to tasks when an employee leaves -- their replacement takes them over or they are assigned to the departing employee's supervisor or a co-worker.

Thus, your application should encode that business practice. The form where you edit the employees should be set up so that when an employee leaves, their tasks are moved to the appropriate employee. This means you don't have to store effective dates and then write the complex SQL to filter by those dates. Instead, you'll use the same structure as you already have.

Now, of course, I'm not stupid -- I know that many companies SHOULD have such policies, but very often, these things fall through the cracks. When I'm in this position (as an outside contractor) I tell them their two options and price the two options. Implementing a policy for what happens with the assigned tasks when the employee leaves is cheap. Implementing an effective date is EXPENSIVE. So, if they want to save money, they'll come up with a policy that can be implemented in the database application.

If the solutions already suggested don't work for you then you could look at creating an audit table which effectively logs every change that is made to a record (This would have the advantage of allowing you to track ALL changes, even in areas of your system you have not yet developed).

Here are a couple of links that show how you could go about doing this:

a simple solution for tracking changes to access data

Allen Browne - creating an audit log.