Social Security Number as a primary key

[Guest]

I am creating a training database. I have used the individuals social
security number as the primary key. I have many tables based on what type of
training they attended. Also, I have a table containing strictly personnel
data, I used their social security number as the primary key as well. The
issue is that when I enter a social security number in the Form that begins
with "0", the "0" gets truncated/cut off. How do I change the properties to
prevent this from occuring?

Also, is using the social security number the best field for a primary key
for each table? I have about 100 individuals and all of them have a social
security number. I want to be able to link all the traing tables and the
personnel data tables together in order to query and run reports.

Thanks for any assistance you can provide.

Regards,
Rick

 

[Rick B]

Is the field a text field or a number? It should be text in my opinion.

Rick B

 

[Paul Overway]

Frankly, I'm of the opinion that SSNs shouldn't be used for anything other
than social security and taxes. In any case, I hope you secured the
database.

 

[JP Bless]

If your database primary key field is "number type" then preceding 0's will
be truncted. If you change the field type to Text then that should be fine.
Unless your app is somehow connected to activities requiring linking
"clients" to their SSN (and as a result) require proper identification
and/or usage of valid SSN is a must, otherwise a sequential computer
generated number can do a good primary key job. Using SSN (as primary key)
in non-Identification sensitive apps is not the best since anyone viewing
your data at form or database level can easily view othe people's SSN. And,
in this day of ID-theft, it is important to think about the fallout of using
SSN as viewable primary key when we users of the can range from the good,
bad and ugly.... Just a thought anyway.

 

[Paul Overway]

Having worked with debt collection databases, I know that SSNs are
frequently used by multiple persons...either because of identity theft or
because the person is here illegally (which is still identity theft...just a
different motive). So, SSNs make a poor ID field anyway. OT...but what
really makes me mad, is now Congress has passed legislation to tie your SSN
into your driver's license...one more avenue for ID theft and potential
gov't abuse.

 

[JP Bless]

Correction...
when we users of the can = when users of the app can range from the good,
bad and ugly... <g>

 

[Guest]

If I don't use a SSN as the primary then how will the autonumber primary key
function be able to link the individuals record from one training table to
another training table? For example Joe Smith attended the following medical
training courses (listed in a table called "medical training"): CPR, First
Aid, Heat Injury Prevention and I want to link this to the table containing
personnel data for Joe Smith and I also want to link it to another training
table called "professional development training" containing the following
classes: Leadership development, Project Managment and Leading Teams. If
you would be so kind as to explain to me, in lay-man's terms, how to link all
these tables together with a primary key, I would greatly appreciate it.

Thanks,
Rick

 

[Paul Overway]

First you need to understand that entries should not be made directly in
tables. But let's get the tables designed and then go on to forms.

It sounds like you have a need to create at least 3 tables:

tblPersonnel
PersID
FN
MN
LN
Etc

tblTraining
TrainID
Type (i.e., Professional , Medical, etc)
Description
etc.

trelPersonnelTraining
PersID (this is a foreign key from tblPersonnel)
TrainID (this is a foreign key from tblTraining)
DateAttend
etc.


So, you need 2 forms, and 2 subforms. First, you'll create a form to allow
entry of records into tblTraining.

Then you'll have a Personnel form which would contain 2 subforms a)
ProfTraining b) MedTraining. The recordsource for Personnel would be your
tblPersonnel table from below. The recordsource for ProfTraining would be a
query which filters trelPersonnelTraining based on the training type...and
the MedTraining subform would need a similar query as its recordsource.
With in the main form, the Link Master and Link Child field properties will
be PersID. So, the only fields you need to show in your subforms are
TrainID, DateAttend, etc.