Voters who are Hunters,Fisherman Query!

[max3002]

I have a Access 2003 database file listing all licensed hunters & fisherman
(purchased from Fish&Game in my home state), about 200,000 names. From
another source I have imported a Voter File list of 45,000 registered voters
names.

Question is: what would be the suggested method to MATCH any records in the
FISH&Game list to the Voters List? GOAL here is to have a LIST of ALL
Fisherman/Hunters who are "Registered to Vote" (voter file list)! Fields in
both files aare basic (Name,Add,City,St,Zip)...

Like many folks, I am near deadline w/ anxious managers and mailing house
waiting for my data list! Again thanks in advance for any help!

 

[[MVP] S.Clark]

Find a matching element between the two tables and link by it. It may be an
SSN, the combination of FirstName MI & Lastname, and/or the full address.

Post the table structures if you need more help.

Save my rights to arm bears!

 

[max3002]

thanks so much for the quick reply. The matching element would be address or
address and name. Table structure is: Hunters,Fisherman: Name(Last,First,
MI),Address,City,State,Zip and the Table for VOTERS is FirstName; LastName,
Address,City,State,Zip....... in other words fields are the same, excpet for
Name.

how would i link the 2 tables....(the voter table has no dubplicate records
and is the table I wish to match up to)......fyi this is for Oregon..land of
great hunting&fishing!
thanks th

 

[John Vinson]

thanks so much for the quick reply. The matching element would be address or
address and name. Table structure is: Hunters,Fisherman: Name(Last,First,
MI),Address,City,State,Zip and the Table for VOTERS is FirstName; LastName,
Address,City,State,Zip....... in other words fields are the same, excpet for
Name.

how would i link the 2 tables....(the voter table has no dubplicate records
and is the table I wish to match up to)......fyi this is for Oregon..land of
great hunting&fishing!
thanks th

Well, you can expect numerous false drops and numerous missed links:
what if the fish & game database has "Smith, Joe" at "4124 Willard",
"Salem", "OR" and the voter registration has "Smith", "Joseph", "4124
Willard Ave.", "Salem", "OR"? Same guy - no match!

Or what if the address is an apartment building with sixty people, 33
registered to vote, 20 hunters? Just joining on the address will give
you 660 matches!

Bearing that restriction in mind, I'd create new FirstName and
LastName fields in (a copy of) the Fish & game table; use an Update
query to update LastName to

Left([Name], InStr([Name], "," + 1))

and FirstName to

Left(Trim(Mid([Name], InStr([Name], "," + 1)))

Then create a Query adding both tables - join FirstName to FirstName,
LastName to LastName, Address to Address, and Zip to Zip (assuming
zips were consistantly added in both tables, otherwise City to City).


John W. Vinson[MVP]

 

[[MVP] S.Clark]

John said it pretty well.

thanks so much for the quick reply. The matching element would be address
or
address and name. Table structure is: Hunters,Fisherman: Name(Last,First,
MI),Address,City,State,Zip and the Table for VOTERS is FirstName;
LastName,
Address,City,State,Zip....... in other words fields are the same, excpet
for
Name.

how would i link the 2 tables....(the voter table has no dubplicate
records
and is the table I wish to match up to)......fyi this is for Oregon..land
of
great hunting&fishing!
thanks th

Well, you can expect numerous false drops and numerous missed links:
what if the fish & game database has "Smith, Joe" at "4124 Willard",
"Salem", "OR" and the voter registration has "Smith", "Joseph", "4124
Willard Ave.", "Salem", "OR"? Same guy - no match!

Or what if the address is an apartment building with sixty people, 33
registered to vote, 20 hunters? Just joining on the address will give
you 660 matches!

Bearing that restriction in mind, I'd create new FirstName and
LastName fields in (a copy of) the Fish & game table; use an Update
query to update LastName to

Left([Name], InStr([Name], "," + 1))

and FirstName to

Left(Trim(Mid([Name], InStr([Name], "," + 1)))

Then create a Query adding both tables - join FirstName to FirstName,
LastName to LastName, Address to Address, and Zip to Zip (assuming
zips were consistantly added in both tables, otherwise City to City).


John W. Vinson[MVP]