tracking history

[Mason Hall]

I am building a database which I want to track current and historical changes
to employee information. For example I want to be able to report on current
employee location, supervisor, etc... and past employee information.

I am stuck with how to get the query to work and how to get a form which
will pull current employee record but also allow me to update just fields
that are changing and create a new record for each time updates occur.

Thus far I have created a parent table (with employee IDs and name) and a
child table (with employee detailed information, unique transaction ID and
transaction date).

Any help is greatly appreciated!

 

[Barry Gilbert]

I think you're on the right track. I would suggest creating validity
date fields in the child table. For example,

EmpId ValidFrom ValidTo Supervisor Dept
1001 1/1/2005 10/30/2005 1005 2
1001 11/1/2005 1006 3

Your query would join on the EmpId field and the criterion for the
ValidTo field would be Is Null. This will give you the current data for
each employee (no end date). If you wanted to pull historical records,
you just query against the two validity dates.

HTH,
Barry

 

[Mason H]

Barry:
Thanks for help! I think that solution will work.

I am still a little stuck on how to:
*Provide a method to select an employee record to update. I was thinking a
flexible parameter query based on name.
*After I select an employee I would like to have the most current
information autopopulate into the form fields and then I would only have to
update those that need to be changed. The rest I would leave and save
entirety as a new record/transaction. I have used the DLookup function
before but not sure if that would execute accurately and cleanly.
thanks again!