Changing key from SS# to auto number

[Guest]

I do not use Soc Sec numbers any more and want to replace it with an auto
number as my primary key. In my table, I did not have the dashes in the SS#
saved, just the number. So, I was wondering if I changed it to auto number,
would it mess up all of the records that are kept already? How would auto
number act if it came across a number that is already in the table? For
example, when just randomly putting numbers in, I might have put 0000000001
as one of the numbers. When I pick auto number, will it start with "1" and
how might that interfear with "000000001" that is already in there? Can I
define the auto number to have 9 numerial placements to keep the format of
previous numbers?

Thanks for your advice!
Jason

 

[Benny]

I do not use Soc Sec numbers any more and want to replace it with an auto
number as my primary key. In my table, I did not have the dashes in the SS#
saved, just the number. So, I was wondering if I changed it to auto number,
would it mess up all of the records that are kept already? How would auto
number act if it came across a number that is already in the table? For
example, when just randomly putting numbers in, I might have put 0000000001
as one of the numbers. When I pick auto number, will it start with "1" and
how might that interfear with "000000001" that is already in there? Can I
define the auto number to have 9 numerial placements to keep the format of
previous numbers?

Thanks for your advice!
Jason

 

[Lynn Trapp]

Jason,
I don't believe you can change a field to AutoNumber after you have entered
data into the table. You can't force an AutoNumber to have a certain number
of digits, but you can display it in a format that shows 9 digits by setting
the format to "000000000".

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Lots of work. First backup the database before you do anything!!

You will need to add another field that is autonumber. Add a new field to
all of your tables that you have set relationships to the SS# and are used in
queries joined with the SS#.
Do an update on all the tables to update the new field with the number in
the autonumber field.
Delete any relationships using SS# and rebuild using the autonumber.
Revise any queries that were joined on the SS# to be joined on the autonumber.