Reusing a number in an indexed field

[James]

I need to keep old employee's name on reports, but I would like to reuse the
employee number. I orginaly had the empID unique, but I just created a
uniqueID for emlpoyee records. I know I should have done that in the first
place! How would I emplement this to a back end db that already has records
in it. All with out removing the empname from the report, let me make the
employee inactive, reuse the old empID (not the newly created uniqueID), and
still only allow one empID? The empID is how we track employee useage.

Part 2: If the first part is possible--How do make this a macro or batch
file to run from a CD-rom or download from the net, or at least make it easy
for the user to incorporate with out me actually doing it for them?
Thanks

 

[Jeff Boyce]

James

First off, speaking as a "old" employee, you might want to call them
"former" employees! <g>

If you have a way to uniquely identify each person, I'm not clear on what
business benefit you derive from have EmployeeID = 1234 assigned to John Doe
between 1/1/1990 and 12/31/1999, and "re-assigning" that same EmployeeID to
Jane Smith starting 10/1/2004?

And if you have ANY related (i.e., "child") tables that use the EmployeeID
as a foreign key, you'll REALLY mess up your database by doing that!

That said, if you are using a (new) unique ID, and the EmployeeID field is
no longer set as your Primary Key, the only thing that might prevent you
from "re-using" EmployeeID is if it is set with a Unique Index.

Oh yes, I'll point out that if you re-use the EmployeeID, then query against
the table for EmployeeID = 1234, you'll get two rows in the above example --
which one do you want?!

 

[James]

Thank you for your help! Sorry, I will use "former" employee! First,
empoyees type in a field there employee id when they enter a transaction.
Then, on the reports screen I am using the employeeID to get the name of the
employee that added a transaction. If an employee leaves the company we
cannot reuse their ID because it would show the new person's information on
transactions the former employee made.