Creating unique ID's

[quietmortal]

Hello everyone.

I have a database in Access 2010 that I've put together to help audit enrollment data. Using the SSN as a unique field for each employee, I was able to put together a system to audit the data for employees. However, I am getting stuck with auditing the dependent data.

The data we receive from vendors does not give a unique ID for each dependent. The dependent SSN is not a required field, so it is most often either missing, or it is filled in with a dummy SSN, such as 999-99-9999.

The basic structure of the database so far consists of three tables. Two tables house employee data and dependent data on our end (they are exported as separate files in our system) and the third table is used to import the employee and dependent data from the vendor's side (they include employees and dependents on the same file).

To perform the actual audit, I have queries set up to pull out and format the employee data from our end and the vendor employee data. Then there is another query which compares those two sets of data together to find the differences, using the employee SSN as a unique key for each record.

The fields I am using in both the employee and dependent queries are: Employee SSN, last name, first name, DOB, Gender, Relationship (employee, spouse or child) Street address, City, State, Zip, Enrollment plan name and Coverage level.

I've been working on this for about a week now and I have yet to figure out how to generate a unique ID for each dependent.

I've tried concatenating various fields together, such as the first and last name, last name and DOB, and employee SSN, last name and DOB. None of the solutions I've tried have been able to come up with a unique ID. Can't use names because there are always several "John Smith's" and I cannot use the DOB because there are several instances of twins. Each time, I end up with a handful of ID's which are duplicates.

I've used solutions from this forum in the past to great effect, which is why I signed up today just to ask this question. I wasn't able to find a similar thread anywhere.

If there is any other information I can provide, please let me know.

Thank you all in advance for any assistance you can provide.

[rpeare]

a unique identifier shouldn't have any relevance to the actual data in your table for exactly the reasons you stated.

Which system are you attempting to assign a unique key or are you trying to apply the same unique key to both the dependent table from your system and the entire list from the vendor employee data?

I think what I would be tempted to do is when you import your data you import it into a table that has an autonumber unique identifier. Then when you import your vendor employee information you can link the data on Primary ssn, birth date, relationship, etc and 'look up' the primary key from YOUR system rather than attempting to get it into two tables.