Table Design Dilemma

[Paul Fenton]

I maintain an application for a client who runs a background search
firm for employers. He has some 40k Subject records and we use the
Subjects Social Security number as the unique, No Duplicate,
identifier to connect those records with records in a couple of other
tables. Obviously, this ID appears in many queries, forms, reports,
etc.

We now have a situation where we're getting applicants who're using
someone else's SSAN when they apply for a job so when my client enters
the number, the program figures this is another search for an existing
subject and fills in the blanks accordingly. Problem is, the search
is for "Bill", not "Bob".

My dilemma is how to now permit duplicate SSAN's in the database.
(Please, let's don't get into the politics of this issue).

I was thinking of adding another field to the Subject table that, in
combination with the SSAN, would be a unique key for each record.
That would require an extensive effort to modify all the queries,
procedures, forms, reports, etc. to accomodate the new field. Not
something I want to undertake if I dont' have to.

Plan #2 was to expand the existing ID field to 10 characters and let
the user append a letter to the social that would make it unigue. I
wouldn't have to mess with queries and coding, just reformat some
forms and reports. Kind of like 123A, 123B... etc.

I'd appreciate some input on this issue, and please, let's keep it to
the Access issues.

Thanks


Paul Fenton

 

[DevalilaJohn]

Paul,

Your idea of adding a character is probably faster and easier. You will,
however need to do a bunch of modifications, otherwise you will be showing a
10 digit value to customers. As we were all led to believe that SSNs are
unique, I think you will find that solving this problem will be the easy part
compared to your client's needing to keep straight who's who.

 

[Paul Fenton]

Thanks for the input John. Yes, you're right, he's going to have a
problem since nothing that goes to my client's client will show the
appended letters.

Paul Fenton