Finding duplicates between records having two Name fields, where names are swapped between fields

Hi,

I do data cleansing involving elimination of duplicate records.  The key reference is address based where I remove duplicates of same address, and same name.  The challenge I face is with tables that can contain two name fields where there may be two or more records that have the same address, and the same names, but those names can be in either of the two name fields. For example

Record #1
Field: Name1 = Jim Smith
Field: Name2 = Susan Smith
Field: Address: = 123 Main St

Record #2
Field: Name1 = Susan Smith
Field: Name2 = Jim Smith
Field: Address: = 123 Main St

Record #3
Field: Name1 = Susan Smith
Field: Name2 = (blank)
Field: Address: = 123 Main St

Record #4
Field: Name1 = Jim Smith
Field: Name2 = (blank)
Field: Address: = 123 Main St

I would like to run a query that would display the results of records where a match was found between the records based on having the same address, and same name (in one of the Name fields).  Ideally I'd want an update query as well, where I would have a field named "Tag", updated with something like i.e. "Duplicate records".

Help would be much appreciated.

Thanks,

Peter
ListriteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
<I would like to run a query that would display the results of records where a match was found between the records based on having the same address, and same name (in one of the Name fields).>
Then please post the *exact* output you need for the records above...
0
Jeffrey CoachmanMIS LiasonCommented:
Also note that another kicker here is if the address is misspelled slightly:
ex:
123 main Street
123 main St.
0
Jeffrey CoachmanMIS LiasonCommented:
There are a host of other contingencies that may also need to be considered.
So then, to me, the simplest thing to do would be to simply identify all the records with duplicate addresses. (an address that appears more then once...)

A simple query would look like this

SELECT YourTable.Address, Count(YourTable.Address) AS CountOfAddress, IIf(Count(YourTable.Address)>1,"Has Duplicate","Has One") AS Status
FROM YourTable
GROUP BY YourTable.Address
HAVING Count(YourTable.Address)>1;


JeffCoachman
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ListriteAuthor Commented:
Hi Jeff,

Appreciate your responses.

First regarding the output, here is a screen shot of what I am looking for:

Screenshot of Results
As for different address names, I already have software that will provide duplicate results of sound alike addresses.  It provides Group numbers based on same addresses.  So I can always use that field instead of the ADD field for a comparison.  However, I always start with exact Address information first for comparison, then move onto sound alike matches.  What I want from Access is the ability to look for names that may be crossed over into other Name fields, and in other records.

Having Access, or for that matter, the address duplication software I have, providing me a list of all duplicates, then reviewing for same names, is simply not feasible for me.  I am working with databases that contain over 550, 000 records.  So with that I do see numerous records that have duplicate addresses, including sound alike addresses.  I already have processes that capture those duplicate addresses, along with duplicate names (including sound alike names).  But those processes do not provide me with a function to seek names that may be repeated, again, in different fields, different records.  

Thanks,

Peter
0
Jeffrey CoachmanMIS LiasonCommented:
OK, lets forget my contingency of the address being misspelled (That was just something else to consider for a different question.)

To get the output you posted for the original sample data, use a query like this:

SELECT IIf(DCount("Address","YourTable","Address=" & "'" & [Address] & "'")>1,"Duplicate Record","") AS Status, YourTable.Name1, YourTable.Name2, YourTable.Address
FROM YourTable
WHERE (((DCount("Address","YourTable","Address=" & "'" & [Address] & "'"))>1));


JeffCoachman
0
ListriteAuthor Commented:
OK, lets forget my contingency of the address being misspelled (That was just something else to consider for a different question.)

To get the output you posted for the original sample data, use a query like this:

SELECT IIf(DCount("Address","YourTable","Address=" & "'" & [Address] & "'")>1,"Duplicate Record","") AS Status, YourTable.Name1, YourTable.Name2, YourTable.Address
FROM YourTable
WHERE (((DCount("Address","YourTable","Address=" & "'" & [Address] & "'"))>1));

Thanks Jeff,

However, that query only produced a list of duplicate records based on Address only.  Regardless, I appreciate your efforts, but have moved away from Access as a solution for this challenge.  I'm now using other duplication detection software.

Peter
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
oK, my post was just to see if I was on the right track
0
ListriteAuthor Commented:
Could not find a solution on EE, so found another solution by using specific software.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.