I tend to use Autonumber PKs for most of my tables, much to the dismay of
the "natural key" purists (heh, heh, heh!). And to build on your
suggestion, Jerry, the OP can simply add another field that is the
"CurrentCompanyEmployeeIdentifier", and THAT can get changed for any reason!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Jerry Whittle" wrote in message
...
I once had the 'pleasure' of trying to fix a problem where a company was
bought by another and, guess what, the employee numbers were all changed!
Since they didn't have Referential Integrity enabled (actually couldn't as
there were some orphaned records) couldn't use Cascade Update.

I'm a firm believer in autonumbers for PKs. If it doesn't have a meaning,
you won't be tempted to change it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Klatuu" wrote:

I think BruceM has a good point. Too many times I have heard "This will
never change". You can never bank on anything staying the same. I had a
similar instance where there was a code convention for a data item that
was
set up to satisfy or ERP system. One a littler over a year, they changed
ERP
systems twice. The coding system had to change both times.
--
Dave Hargis, Microsoft Access MVP


"BruceM" wrote:

I am in a situation where they changed the format of the EmployeeID
number.
Perhaps updating thousands of records in related tables for a hundred
employees is simpler than I imagine by using Cascade Update Related
Fields,
but I am not anxious to attempt the project. I am left either with
doing
the update (and verifying that it has gone as planned) or using the old
number as the PK (incrementing it by 1 for new employee records) and
adding
another field for the new ID number.

"dee" wrote in message
...
Seriously... if I have, for example, employee numbers in an Employees
table,
should I use the employee number (will never change) or should I add
an
autonumber PK?
--
Thanks!

Dee