Customer DB Duplicates

[asamaka]

I have a table with 15000 entries of our passholders from different seasons.
Most of them are repeat customers therefore duplicates. I need:

1) A list of de-duped passholders (Show unique records by hiding duplicates,
possibly including check-box for each season)
2) A list of de-duped passholders that owned the pass any of the previous
years but not last season.

The problem is that two passholders can have the same full name but be two
different persons or the address from the same repeated passholder can be
typed with little differences (like a capital letter or a space) so that I
can't find the way to make the right query.

I am using Access 2007 and I would highly appreciate any help

Thank you

 

[Jerry Whittle]

GIGO: garbage in garbage out. How about passholders who have changed their
name because of something like marriage? Are things like Junior, Senior, II,
etc., tracked? If not there can be dupes there. People moved to another
address?

Chances are very good that you won't be able to do this task with a high
degree of accuracy. Short of having something like everyone's Social Security
Number, your best bet is attempting to join up records by names then
addresses. With some human eyeballs on the job, you might be able to clean up
the data enough to get some answers.

There are companies out there who specialize in cleaning up this kind of
data. They are not cheap.

 

[GBA]

well Jerry is right....but I sympathize on your lack of perfect world....

with a query of this table, make a new column from the address and estimate
as to how many characters are sufficient to consider it a match. After all -
think about it - 2 different people may have the exact same name but the odds
that they have the same street number also are quite low....so maybe you only
need to make a column that uses the 3 most left characters ShortAddress:
Left([FieldName],3)

By only using the 3 most left characters you eliminate all the
typo/variations that will occur in the remainder of the address...but still
get some unique distinction between John Smith 1 Main St vs John Smith 11
South St.

So now do your no duplicates (Distinct) query using fields Name & Short
Address

hope this helps...

 

[asamaka]

Well, thank you for your help but I've given up.
I have people who spelled their names in 5 different
ways throughout the years..

well Jerry is right....but I sympathize on your lack of perfect world....

with a query of this table, make a new column from the address and estimate
as to how many characters are sufficient to consider it a match. After all -
think about it - 2 different people may have the exact same name but the odds
that they have the same street number also are quite low....so maybe you only
need to make a column that uses the 3 most left characters ShortAddress:
Left([FieldName],3)

By only using the 3 most left characters you eliminate all the
typo/variations that will occur in the remainder of the address...but still
get some unique distinction between John Smith 1 Main St vs John Smith 11
South St.

So now do your no duplicates (Distinct) query using fields Name & Short
Address

hope this helps...

I have a table with 15000 entries of our passholders from different seasons.
Most of them are repeat customers therefore duplicates. I need:

1) A list of de-duped passholders (Show unique records by hiding duplicates,
possibly including check-box for each season)
2) A list of de-duped passholders that owned the pass any of the previous
years but not last season.

The problem is that two passholders can have the same full name but be two
different persons or the address from the same repeated passholder can be
typed with little differences (like a capital letter or a space) so that I
can't find the way to make the right query.

I am using Access 2007 and I would highly appreciate any help

Thank you