ACCESS FEED: Re: [MS_AccessPros] Using multiple name criteria to match

Selasa, 14 April 2015

Re: [MS_AccessPros] Using multiple name criteria to match

Hi Bill:

No worries. I have a file of 9,000 voters which is provided by the Board of Election to the candidates upon request. I have loaded this information in the VOTER_REGISTRATION table. The candidate I am helping has 500 contacts from his own files. In this case, he has provided name and email address but no physical addresses. I have loaded this into the CANDIDATE_CONTACTS table. I want to map each record in the CANDIDATE_CONTACTS table to their record in the VOTER_REGISTRATION table.

I get this kind of request from local candidates a lot. Sometimes i have addresses as well but there may be 4/5 voters registered to one household. In this case, I don't have an address so I have to match on name.

When I go though the records manually, I see a lot of CANDIDATE_CONTACTS records with Bill Smith but they are listed as William Smith in the VOTER_REGISTRATION.

My goal is just to record the Voter ID number assigned by the Board of Elections to as many of the candidate's contacts as I can. I don't want to change "Bill" to "William" on the CANDIDATE_CONTACTS table. That would actually be counter productive for email and snail mail communications.

Clear as mud?

Thanks

Jan

On Apr 14, 2015, at 8:27 AM, wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

So you are dong this check against data with the proper name already known? Why bother to clean up the name when all you need is the ID?

Sorry for my confusion.

-Bill

---In MS_Access_Professionals@yahoogroups.com, <jan.hertzsch@gmail.com> wrote :

Thinking about it, my problem is less complex than I first thought. Regardless of the nickname (Bill, Billy, Willy) I want them to come up with the "proper" first name (William). So my crosswalk table has several sources to one target.

Bill>William
Billy>William
Willy>William

I create a query that selects only the records from my Patrons which have a blank in the VOTER_ID field and use that to create a new table (Temp_Voter_Match?) using the same prime key as the Patrons table.

I do an update query to on Temp_Voter_Match table replace the nickname with the "proper" name.

I run theTemp_Voter_Match table against the Voter Registration table now matching "proper" first name plus last name and updating the Voter_ID field with any hits

Finally, I update the VOTER_ID field in my PATRONS table using Temp_Voter_Match table. Since they have the same primary key, it should be easy to match.

I have not tried this yet but I think it will work. I just have to overwrite the Temp_Voter_Match table every time.

Comments?