Managing a data list and removing duplicates

[Paul Mendlesohn]

I have a list of 12,000 names and addressed. A number of them are duplicates
in that when there is a family there may be two or more members of the same
family. I want to delete any duplicate records and change the remaining
record title to Mr and Mrs, or just Mr or Mrs if there is one carer and the
rest are kids.
Is this possible with Access programming? Does anyone have any code I could
use.
Thanks

 

[RD]

It *is* possible *if* you have a way to tell that the entries are, in fact,
members of the same family. At my job I often run into multiple instances of
names where the people are *not* related (especially true of Hispanic names).
One can do some pretty cool things with code but it can't perform magic or read
minds.

So, do you have a way to absolutley identify members of the same family?

Regards,
RD

 

[Klatuu]

I would start experimenting by filtering on some of your fields to look for
duplicates. You could use the Find Duplicates Query Wizard as a start. Look
at Last Name, but don't depend on it. There can be a lot of duplicates that
are not the same, and some household may have different last names. Try
looking for duplicate addresses. Hopefully there is a consistency in data
entry so you don't end up with
1st Street, First Street, 1st St., First, First St., etc.
This is an age old programming problem with no simple solution. I know you
have the list alread, but think in the future about structuring your data
entry so there is consistent data, and include checks in your data entry
form's code to look for duplicates as they are entered.

Best of Luck, you are going to need it.

 

[Paul Mendlesohn]

I obtain each database list from a dental practice this is something I will
be doing on a regular basis with new lists of varying types. I will manually
put the lists into a standardized format.
It is only imortant that I write to one person at each address so I can sort
the addresses by post code and first line of address. The other problem is
that I don't want to write to Mr and Mrs if there is a single parent family.
It wouldn't matter terribly if more than one person at some addresses
received a letter, it's just that overall I have 12,000 roecords with 6,000
dupilcates. Removing most of the duplicates will save a fortune on the
mailing.
thanks
Paul

 

[RD]

Ok, but you still need some kind of criteria by which you define which records
to use. For instance, if you did want to restrict the mailing to one letter per
address, AND the addresses were entered in a uniform way ("22 Pine St." is not
equal to "22 Pine Street"), you could run a "Find Duplicates" query. The point
is, *you* have to come up with a way to tell Access which records to pay
attention to. See what I mean?

Regards,
RD

 

[Paul Mendlesohn]

Yes that's right. I can do the query but it shows me that 6,000 records are
duplicated. That is only the first stage. What I then need to do is to remove
duplicates and change the 'Title' field of the ones left depending upon
certain criteria such as is there a Mr or Mrs in the duplicated fields. It's
this second part that I don't know how to do programatically.
Thanks
Paul

 

[PC Datasheet]

You would use an update query based on criteria you set.

On another topic, does the dental practice need an appointment scheduling
module? I have such a module and if you are interested, contact me at my
email address below and I will send you a screen shot.

 

[RD]

I tend to be a data keeper. I wouldn't actually remove the duplicate records
unless I was sure I neither needed nor wanted them. In your case I think I
would add an extra field to your table called maybe "MailFlag" or something.
Then I would go through and assign a value of "Y" or True or whatever to the
records you decide are the proper ones. Then you just end mail to those
records.

So, you would set up an update query based on your find duplicates query.
Update your new flag field, with whatever you decided to use, where "Title" =
"Mr." or something along those lines.