How to deal with SSN Numbers and dashes.

[OasisUnknown]

Hello All,

I had some basic/intermediate questions on how you guys would deal with social security numbers. I am working with several systems that I am combining raw data into my access database.

I am trying to make this database as easy to maintain and update as humanly possible because when I am gone there will be no one to maintain or even update the database so it really needs to go the distance for a while to keep the people that I work for going for a while.

The problem that I am running into is with social security numbers. I am using them as the relational key for all of my tables in my database because they make the most sense.

But I have a few situations that I need to solve.

the SSN come from the raw data formatted three different ways.
1.123-45-6789 (formatted with dashes)
2.012345678 (formatted no dashes)
3.12345678 (formatted no dashes and leading zeros missing.)

for the database I would prefer for all the references to have all socials formatted this way.
2. 012345678 (formatted no dashes and preserved leading zeros).


I only have a few tables of source data in my database and a few tables to hold persistent data.
Each table should hold anywhere from 500 - 1500 records. I dont anticipate any more than that at any given time.


In my self pondering I was thinking about having the source data pulled into the database unchanged and then either use an update query on the data to remove dashes and add leading zeros but I dont really know how to go about this.

or I can make a select query of each table with SSN's on it and use a calculated field to manipulate the data.


I would think that using an update query to format the data on upload would probably be best because it makes the change one time and then it is persistant for the rest of the life of the table.

I just wanted to run it past you guys and see what you pro's would do.

 

[Joe4]

There are various different ways to go about this.
One simple (but manual) one is to open your table, highlight your SSN column and do a Find and Replace replacing all "-" with "".
Of course, you would need to do this every time imported data.

Another way is to do the following:
- Import all data "as is" into a temporary table whose structure is a mirror image of your main table (but with no data in it)
- Create an Append Query which writes the data from this temporary table to your main table
- In this Append Query, use this calculated field for SSN instead of the SSN field:

SSN: Replace([SSN],"-","")

This will write the SSN without dashes to your Main Table
- Delete the data out of your temporary table when finished, to prepare for the next data import.

 

[OasisUnknown]

Thanks for the reply Joe4.

I solved my first issue with an update query to remove the dashes using the replace. It was super simple.

But how would you tackle updating the socials that come in with leading zeros missing? (ie 23456798 where it should be 02345678 or 3456789 vs 003456789)

The only thing I could think of doing is doing a length check on the social and if it is not 9 characters in length then add leading zeros until it meets the 9 length requirement.
but this seems overly complicated.

 

[james_lankford]

people actually have SSNs with leading zeros ?

how old are those people ?

then I'd to and insert or update like this
right("000000000" & Replace([SSN],"-",""), 9)

 

[OasisUnknown]

I was actually doing some research on socials just the other day actually and it said that the first three did gets of the ssn is based on area code. Not age.

Just a fun fact.

But just to break down your suggestion your saying I should take a right function that is 9 chars in langth. Format it with 9 0's and then remove the dashes all in one go.

I like that suggestion. I will get back to you with how it works.

 

[xenou]

But just to break down your suggestion your saying I should take a right function that is 9 chars in langth. Format it with 9 0's and then remove the dashes all in one go.

More precisely, the function given is appending 9 leading zeros to an ssn that may or may not already be 9 characters in length. Then taking the right 9 characters whatever. Formatting would be another option.