HELP MATCHING PHONE NUMBERS ON VOTER LIST [combining lists from different sources]

[ciphersankofa]

I have two lists.

one list is a contact list of voters that voted in all the past 5
elections. there are no phone numbers on this list (2226 records).

the other list a list of voters in general in the area. this list
includes telephone numbers (23,214 records).

I got these lists from different people so 'John Smith' in list 1
doesn't have the same primary key as 'John Smith' in list 2.

What I'd like to do is have a list with all the records from list one
with the corresponding phone number from list 2.

i can manually look up people's names and addresses and cross
reference the data so i'm assuming there's an automated way to do it.

i'm trying to call only the voters in list 1..

is there a way to do this?

please help me... i have such limited time. school board races are in
full swing..

thanks in advance

 

[Guest]

do you have First, last, and Middle names in both tables, or just first and
last? Are these values is separate fields, or in a single field?

If they are in separate fields, you can try something like:

Select tbl1.ID, tbl1.Last_Name, tbl1.First_Name, tbl2.PhoneNum
FROM tbl1 LEFT JOIN tbl2
ON tbl1.Last_Name = tbl2.Last_Name
AND tbl1.First_Name = tbl2.First_Name
AND tbl1.MI = tbl2.MI
ORDER BY tbl1.Last_Name, tbl1.First_Name

If you don't have the middle initial in both tables, then this becomes
problematic as there may be more than one John Smith in either list. This
will still be a problem with the MI, but nearly as likely. You could end up
with:

John Smith #1 -> John Smith #1
John Smith #1 -> John Smith #2
John Smith #1 -> John Smith #3
John Smith #1 -> John Smith #4
John Smith #2 -> John Smith #1
John Smith #2 -> John Smith #2
John Smith #2 -> John Smith #3
John Smith #2 -> John Smith #4

But it will give you a good starting point. Once you get this query
working, I would recommend that you do a "DISTINCT" query on phone number, so
you don't call the same house more than once.

HTH
Dale

 

[ciphersankofa]

do you have First, last, and Middle names in both tables, or just first and
last? Are these values is separate fields, or in a single field?

If they are in separate fields, you can try something like:

Select tbl1.ID, tbl1.Last_Name, tbl1.First_Name, tbl2.PhoneNum
FROM tbl1 LEFT JOIN tbl2
ON tbl1.Last_Name = tbl2.Last_Name
AND tbl1.First_Name = tbl2.First_Name
AND tbl1.MI = tbl2.MI
ORDER BY tbl1.Last_Name, tbl1.First_Name

If you don't have the middle initial in both tables, then this becomes
problematic as there may be more than one John Smith in either list. This
will still be a problem with the MI, but nearly as likely. You could end up
with:

John Smith #1 -> John Smith #1
John Smith #1 -> John Smith #2
John Smith #1 -> John Smith #3
John Smith #1 -> John Smith #4
John Smith #2 -> John Smith #1
John Smith #2 -> John Smith #2
John Smith #2 -> John Smith #3
John Smith #2 -> John Smith #4

But it will give you a good starting point. Once you get this query
working, I would recommend that you do a "DISTINCT" query on phone number, so
you don't call the same house more than once.

HTH
Dale

thanks so much for this... i don't have middle initials, but i do have
addresses. would that work?

 

[ciphersankofa]

do you have First, last, and Middle names in both tables, or just first and
last? Are these values is separate fields, or in a single field?

If they are in separate fields, you can try something like:

Select tbl1.ID, tbl1.Last_Name, tbl1.First_Name, tbl2.PhoneNum
FROM tbl1 LEFT JOIN tbl2
ON tbl1.Last_Name = tbl2.Last_Name
AND tbl1.First_Name = tbl2.First_Name
AND tbl1.MI = tbl2.MI
ORDER BY tbl1.Last_Name, tbl1.First_Name

If you don't have the middle initial in both tables, then this becomes
problematic as there may be more than one John Smith in either list. This
will still be a problem with the MI, but nearly as likely. You could end up
with:

John Smith #1 -> John Smith #1
John Smith #1 -> John Smith #2
John Smith #1 -> John Smith #3
John Smith #1 -> John Smith #4
John Smith #2 -> John Smith #1
John Smith #2 -> John Smith #2
John Smith #2 -> John Smith #3
John Smith #2 -> John Smith #4

But it will give you a good starting point. Once you get this query
working, I would recommend that you do a "DISTINCT" query on phone number, so
you don't call the same house more than once.

HTH
Dale

HEY!!! IT WORKED!!! THANKS SO MUCH!!!

 

[Dale Fye]

Glad I could help.

The full address doesn't usually work too well because there are so many
ways to appreviate the Street, Road, Blvd, Court, ...

But if you had the street number, that would probably cover about 98% of you
duplicate names. What are the chances you have John Smith living at 100 1st
Stree and 100 Maple Rd?

Dale