Archive Function

[Guest]

Hi,

ive created a database that tracks our employees which move around quite
alot. i need to keep track of who is in what job at a particular date and
time.

in order to keep historical records, i need an archive function that copies
one record to another table and gives it a unique identifier,,,, is this
possible and can anyone help

thanks in anticipation of help...

Paul

 

[Allen Browne]

Don't use another table, just a field to mark the record inactive.

Presumably you have a table of employees, and another table to record the
jobs they do/did. This second table would have fields:
EmployeeID foreign key to Employee.EmployeeID
JobID foreign key to the job (or dept? or perhaps a
freeform text field?)
StartDate when the person started doing this job.
EndDate Blank, until the person terminates the job.

You can now very easily get the job the employee currently does:
WHERE EndDate Is Null

This structure also copes with an employee doing multiple jobs concurrently
(e.g. job sharing or part time with different departments).

 

[Guest]

Allen, thanks for your advice, unfortunately in when i set up the structure
of the dbase i rushed the job.. i know im going to have to resolve this in
the future but..

my main table has employee, job and replacment all together; if i had my
time again i'd put them all in separate tables...

what i need to do now to create the archive function is put a button on the
form thats shows the data in the main table and when that button is pressed
it copies the record and deposits it in another table the archive table which
will have exactly the same fields as the main table...any thoughts,,,

paul

 

[Allen Browne]

If you want to go that route, see:
Archive: Move records to another table
at:
http://members.iinet.net.au/~allenbrowne/ser-37.html

 

[Guest]

Paul,

Would you be willing to share your employee database with me? I'm a Human
Resources Director and we don't currently have a good employee database.

Bronda