Linking Unlike Information

[Guest]

I have two tables that I need to link with social security numbers. However,
in one table the ssn is all 9 numbers while in the other table it is 5 xs and
4 numbers (xxxxx1234). This is the only like field as the names are also in
different formats, one with the whole name and the other tbl is broken down
(last, first, mi). The only way I can think of to link them is to create a
third table matching the ssn's and then linking all three. Am I on the right
track? Is there a better way?

 

[Joseph Meehan]

I have two tables that I need to link with social security numbers.
However, in one table the ssn is all 9 numbers while in the other
table it is 5 xs and 4 numbers (xxxxx1234). This is the only like
field as the names are also in different formats, one with the whole
name and the other tbl is broken down (last, first, mi). The only
way I can think of to link them is to create a third table matching
the ssn's and then linking all three. Am I on the right track? Is
there a better way?

Are you aware that with the information you have it is not possible to
guarantee accuracy? Names can and do duplicate and with less than a full SS
number, that number will also duplicate?

 

[John W. Vinson]

I have two tables that I need to link with social security numbers. However,
in one table the ssn is all 9 numbers while in the other table it is 5 xs and
4 numbers (xxxxx1234). This is the only like field as the names are also in
different formats, one with the whole name and the other tbl is broken down
(last, first, mi). The only way I can think of to link them is to create a
third table matching the ssn's and then linking all three. Am I on the right
track? Is there a better way?

There are somewhat more than 10000 people with SSN's - but only 10000 possible
four digit combinations. I don't think you CAN unambiguously match these two
tables. The names probably won't help much either, as (if your tables are
anything like ones I've dealt with) you'll have variant spellings, name
changes, middle names present in one and not in the other, and so on.

The best you'll be able to do is a (probably very slow) query like

SELECT <whatever you want to see>
FROM Table1, Table2
WHERE Right([table1].SSN, 4) = Right([Table2].SSN, 4)
AND Table1.FullName LIKE "*" & Table2.FirstName & "*"
AND Table1.FullName LIKE "*" & Table2.LastName;

but this will almost surely include both missed joins *and* false drops.

John W. Vinson [MVP]