Re: Archive Function

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 11/22/04 

Date: Mon, 22 Nov 2004 16:50:04 +0800

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). 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"paul" <paul@discussions.microsoft.com> wrote in message
news:CE5967CC-35F3-4450-A575-05B22F6B3024@microsoft.com...
>
> 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

Relevant Pages