how to manage left employees

[MissVinegar]

Hi,
I'm upgrading a personnel database. At the moment there is only one table and when an employee leaves his record is copied to the identical table and then removed from the main table. Maybe it's not the most sophisticated way of doing this but it's ok. But I wanted to develop this database, add related tables. I would like to keep ALL info about left employees. How can I do this?
I'd like to mention that every employee has a primary key (a number) that can be given to a new employee once the number becomes free.

[recyan]

Hi,

Would not suggest removing the employee from the table & also not reassign the ID to another employee. Instead have an additional column, say STATUS in which you can have values like CURRENT OR PAST.

Thanks

[MissVinegar]

I'm afraid the employee number must stay as it is - only from 1 to let's say 100. I could make the employee number just a text field and use autonumber as a primary key. Obviously I want my past employee well out of the way so if I was using your method I would have to have all forms based on a query (based on the main table) that would be picking up only current employees? Am I thinking in the right direction?

[recyan]

Not an expert, but all the same a few thoughts :

1) Do not know your situation, hence am unable to understand the necessity of employee number being limited to a range & its being reused.

2) If you use an additional let us say Autonumber field as primary key for each employee, then what purpose does the employee number (the one which you re-assign) serve (how will you use it)?

3) Will try to give a few thoughts that come to my mind, as the employee database expands in terms of functionality
a) We store the personal details eg. Contact details, Date of Birth, Unique SSN, etc no, etc
b) We store the employee employment period details i.e. Date of Joining, Date of Resignation (I am mentioning this seperately, in case there is a possibility of an employee leaving the organisation & then rejoining the organization at a later date. i.e. the same employee has two or more different periods of employment with the organisation).
c) We start storing employees attendance (present, absent, overtime,etc) records.
d) We start storing emloyess salary details
e) etc
f) If we reassign the employee id, let us say ID No 2 to Smith & after some time after Smith leaves, we reassign the number to Jones & then to Kelly, then how do you track which personal detail, attendance record, salary record, etc belongs to whom
g) If we move employees who have left the organisation to another table, then do you plan to have seperate queries, forms, reports, etc for these in case you need to find out some details for a past employee
h) In case, the number of records is going to grow beyond control which is the reason for shifting past employees then it is OK to have seperate tables, but then it needs to be carefully though over.

so if I was using your method I would have to have all forms based on a query (based on the main table) that would be picking up only current employees?

This depends on what you are trying to do & it's not something to worry about.
If you want a list of all current employees, then the query will need to use the ACTIVE criteria.
You will be mostly accessing individual Employee records based on their ID.

Thanks