How to change Primary Key value in existing database?

[VictorS]

Hello everybody!
I’ve inherited a moderate size Employee database.
Main Employees table has a SSN as a primary key, but it is a “Text” data type.
I’m now told to delete social security numbers due to security reasons, change primary key to preferably autonumber or other unique value, but still keep last 4 digits as a new field.

tblMain structure
SSN;Text; primary key
LastName; Text
FirstName;Text

This table is in one to many relationship to 10 other tables.
Recently I selected “Cascade Update Related Fields” in order to update some incorrect numbers.

Here are my thoughts:
Using Right([SSN],4) I will create a new field in tblMain to track last 4 digits.
This field unfortunately has to be a “Text” in order to run append query.
As for changing SSN field to autonumber- no solution yet.
I could manually update SSN to unique text using first 3-4 letters of the name, but how to make users to apply same technique when entering new employee?

Any help is greatly appreciated!

Victor

[VictorS]

Correcting myself:
I cannot use autonuber in tblMain- it will allow duplicate entry.
Here is what I did so far:
Used Update quert to create field "Last4SSN" using expression
Right([SSN],4)
Used Update query to create field "NewId";Tex t data using expression
Left([LastName],1) & Mid([FirstName],1,1) & Right([Last4SSN],4)
"NewId" field appears to be unique.
Next step- manually update SSN field to new values of "NewId"
I guess I have keep fields name as is (SSN) in order to make everything work.

[Rabbit]

Is this a make table query? I assume it is.

The problem with your expression is that with enough people you're eventually going to have 2 people with the same ID.

What you can do is use a multi column primary key. After the table is made, go into table design view and set the last name, first name, and last 4 SSN as the primary keys.