Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 4th, 2006, 07:08 AM
Authorized User
 
Join Date: Apr 2006
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default Name and Address Comparison

Hi,
I often do a task called the “merge and purge”. Basically, before doing a marketing campaign to offer a specific product, I need to make sure that the lead file is cleansed. A part of the cleansing process is to remove all active members in our database for a specific product from the incoming lead file.
The cleansing is done in two ways:
1-Based on a unique identifier. (Eq: Match the phone number of the active members to the phone number in the lead file, if there is a match then records are deleted from the lead file).

2-The dilemma is that in some cases I do not have this unique identifier, so I do the matching based on the fist name, last name and address. Basically, I take the first 3 characters of each field on my database and I compare them to the same set of characters in the lead file.
[u]Eq: </u>
strcomp( left(Active_FirstName,3), left(Lead_FirstName,3))
strcomp( left(Active_LastName,3), left(Lead_LastName,3))
strcomp( left(Active_Address,3), left(Lead_Address,3))

If the result is equal to 0 for the 3 compared sets, then I know that this is the same person and I delete the record form the lead file.

If else I treat them as 2 different people.

Doing the comparison as mentioned above made me miss a lot of names that must be removed from the incoming lead file.

Now, my question is the following:

Is there any other way to do the comparison?
Eq:
>>>>>>>>>>Active subscribers<<<<<<<<<<<<<>>>>>>>>>>>>>Lead <<<<<<<<<<<<<<
----[u]FName</u>----------[u]Lname</u>-----------[u]Add</u>-----*****-------[u]FName</u>---------[u]Lname</u> -----------[u]Add</u>-----
.....John.................Smith........12 anywhere...******.............J.................Sm ith......12-anywhere
....Susan............Clarkson........5anywhere.... ..********.......Suzan.............Klarkson....... ...#5anywhere
....Rohan.............Muller..............10 RR15......******.......Dr Rohan .........Muller.............10 RR#15

How can I build logic to compare these names and make sure to remove them from the lead file? Any tips and tricks on this?


Thanks
Sayed ben aycha
  #2 (permalink)  
Old May 4th, 2006, 10:43 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

First off, I would not use the logic of the first three characters of those fields. You want to compare ALL the characters. You're liable to delete someone you don't want such as this:

Louis Smith 123 Main Street
Louisa Smithsonian 123 Elm Street

Yikes!

Also, using address is tricky because people tend to enter them so differently for the same address. Using first name and last name and something more rigid is desireable, such as ZIP code... especially if you require people to enter the full nine-digit ZIP code. That would zero in on people much better.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #3 (permalink)  
Old May 5th, 2006, 05:20 AM
Authorized User
 
Join Date: Apr 2006
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
Are there any other ideas out there in regards to how to deal with the issue above?

I have seen a demo for software that performs the “merge and purge” based on Name and address. Basically you load your main file plus one or more kill files, everything is merged together, and the software loops through your files, and then output all the duplicate records within the main file and duplicates from other kill files.
(Records exist in main and kill)

I wish I understand the logic behind that software.

Thank you.
Sayed


  #4 (permalink)  
Old May 16th, 2006, 06:40 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I think the important thing here is to build the database properly in the first place. Why is the same data being entered twice? It is because it is entered twice that the users are able to enter the information differently, and thus make your logic unworkable.

I would restructure the database so that a person was entered ONCE in a Customer table, and then that data could be pulled into both the Lead and Active Subscriber tables as needed. If you did this, you could also perform logic to automatically remove the customer from the Lead table when they are added to the Active Subscriber table.

Barring this solution, perhaps you should add your logic to the Active Subscriber process. Instead of having that data entered a second time, when your sales people create and active subscriber, they should do it by transferring the data from the Lead table to the Active subscriber table with a button click, thus purging the Lead table in real time, and thus not requiring this sort of batch process. We can totally help you with that.

HTH

mmcdonal


Similar Threads
Thread Thread Starter Forum Replies Last Post
datetime comparison MunishBhatia ASP.NET 2.0 Professional 2 May 24th, 2007 07:42 AM
Date comparison lily611 General .NET 5 January 4th, 2005 07:08 AM
Table Look Up and Comparison mmcdonal Access VBA 1 November 17th, 2004 05:28 PM
Date Comparison hoffmann Classic ASP Databases 7 October 21st, 2004 09:00 AM
Retreiving IP address & gateway address sjangit VBScript 0 February 3rd, 2004 02:02 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.