Primary Key Downsize

[Scarlett]

I currently use social security number for primary key in my Database. I
would like to keep the last 6 digits only of this number without losing all
25,000 records.I am able to downsize the number by changing the size of the
field, however, it keeps the FIRST six digits and I need to keep the LAST 6
digits.Can someone help me?

 

[Larry Linson]

I currently use social security number for primary key in my Database. I
would like to keep the last 6 digits only of this number without losing
all
25,000 records.I am able to downsize the number by changing the size of
the
field, however, it keeps the FIRST six digits and I need to keep the LAST
6
digits.Can someone help me?

I hope you are working on a copy of the database!

Create a new Field, and assuming you are keeping the SSNs as Text, use

NewId: Right(yourSSNfieldname, 6)

in an Update Query, replacing NewID with the name you use for the newly
created Field and yourSSNfieldname with the name of the Field where you have
the SSN stored. If you specify the new Field as indexed, No duplicates, it
will catch any errors. You could also create a new Table with a MakeTable
Query, if you'd prefer.

Larry Linson
Microsoft Access MVP

 

[KARL DEWEY]

The field needs to be a text field and just do an update. Open a select query
in design view and right click in the open area where the table is shown and
move the cursor to Query Type and click on Update. In the Update row for the
YourSSNField put this --
Right([YourSSNField],6)

 

[John Vinson]

I currently use social security number for primary key in my Database. I
would like to keep the last 6 digits only of this number without losing all
25,000 records

What makes you think the right six digits will be unique? The first
two digits of a SSN are related to the place of birth; might you not
have (say) two entries which differ in the first two digits only, say
655-55-5555 and 925-55-5555?

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

In addition, SSN is a poor choice as a primary key for other reasons related
to privacy and lack of control over uniqueness. Plus, foreigners working in
the US may have different worker id's that are not SSN's.

 

[Scarlett]

I agree. Do you have any better ideas for a unique number? I would prefer to
keep my existing data but if that is not possible I'm even looking for a new
Unique ID Formula for a database of this size and that is continually
growing. Any suggestions?

 

[John Vinson]

I agree. Do you have any better ideas for a unique number? I would prefer to
keep my existing data but if that is not possible I'm even looking for a new
Unique ID Formula for a database of this size and that is continually
growing. Any suggestions?

A concealed Autonumber is one choice; a "custom counter"
programmatically assigned sequential long integer is another.

John W. Vinson[MVP]

 

[Scarlett]

My problem is that when someone calls in later down the road even years, and
would like a printout of training, I need to be able to produce that
instantly. Currently I ask them for their SS# and am able to identify them
immediately; looking them up by name is not an option because there are
duplicate names.

 

[KARL DEWEY]

Many Human Resource organization have switched from SSN to Employee Code, a
unique number assigned to each employee. The same number is used even when
re-hired.

 

[Scarlett]

We are a third party entity doing training for "Our Member Companies". We
have over 900 members and each have their own workers. (Construction
Industry). The workers may move from company to company from year to year,
month to month, and even week to week. Alot of these companies will be
competitors so there is not one certain company that would give the employee
a unique number and/or keep that number in their files. I have successfully
taken the SSN field down to 7 numbers without duplicates. I think this will
suffice, do you see any forthcoming problems in using the last 7 digits of a
social security number? Oh, by the way, thank you so much for giving me the
formula in which to create the update query!I had received it from a couple
of others but there were not brackets and I kept update the field to "SSN"
rather than the actual digits. You're a real PAL!--
Scarlett

 

[KARL DEWEY]

I think you it would be better if you used an autonumber for employee code
and keep SSN in a reference table.

 

[Pat Hartman\(MVP\)]

Use an autonumber as your primary key. Store the entire SSN in the member
record and add a unique index on the SSN. You can search a table by ANY
field or combination of fields. The field you are searching on does not
need to be the primary key. Adding a unique index on SSN will spread up
searches when SSN is used as the lookup field.

 

[John Vinson]

My problem is that when someone calls in later down the road even years, and
would like a printout of training, I need to be able to produce that
instantly. Currently I ask them for their SS# and am able to identify them
immediately; looking them up by name is not an option because there are
duplicate names.

Then store the SSN in an indexed field and look IT up - without using
it (or a munged version of it) as a primary key.

It's *not* necessary to have a field as a primary key in order to sort
or search by it!

John W. Vinson[MVP]