Find suspected duplicates

[cmk7471]

I'm trying to create a list of possible duplicate customer accounts that can
be reviewed by CS reps. I'm starting with one table with all the customer
data. Using that I made a table (TBL-MatchData) with the fields I want to use
to determine if it may be a duplicate acct (last name, first 4 chars of
address, city, & state). Then I created the query below that gives me a list
of pairs of acct numbers that match using the criteria I mentioned.

SELECT [TBL-MatchInfo].ACCT AS ACCT1, [TBL-MatchInfo_1].ACCT AS ACCT2
FROM [TBL-MatchInfo] INNER JOIN [TBL-MatchInfo] AS [TBL-MatchInfo_1] ON
([TBL-MatchInfo].LNAME = [TBL-MatchInfo_1].LNAME) AND ([TBL-MatchInfo].ADDR =
[TBL-MatchInfo_1].ADDR) AND ([TBL-MatchInfo].CITY = [TBL-MatchInfo_1].CITY)
AND ([TBL-MatchInfo].STATE = [TBL-MatchInfo_1].STATE)
WHERE ((([TBL-MatchInfo].ACCT)<>[TBL-MatchInfo_1]![ACCT]));

The only problem is that each pair of acct numbers shows up twice, one is
just the reverse of the other.

Acct1 Acct2

A B
B A
C D
D C

How can I end up with a set of unique combinations of the acct numbers?

Acct1 Acct2
A B
C D

 

[John Spencer MVP]

You might try modifying the where clause as follows.

SELECT [TBL-MatchInfo].ACCT AS ACCT1, [TBL-MatchInfo_1].ACCT AS ACCT2
FROM [TBL-MatchInfo] INNER JOIN [TBL-MatchInfo] AS [TBL-MatchInfo_1]
ON ([TBL-MatchInfo].LNAME = [TBL-MatchInfo_1].LNAME)
AND ([TBL-MatchInfo].ADDR = [TBL-MatchInfo_1].ADDR)
AND ([TBL-MatchInfo].CITY = [TBL-MatchInfo_1].CITY)
AND ([TBL-MatchInfo].STATE = [TBL-MatchInfo_1].STATE)
WHERE [TBL-MatchInfo].ACCT<[TBL-MatchInfo_1]![ACCT]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[cmk7471]

Perfect! Thanks so much!

You might try modifying the where clause as follows.

SELECT [TBL-MatchInfo].ACCT AS ACCT1, [TBL-MatchInfo_1].ACCT AS ACCT2
FROM [TBL-MatchInfo] INNER JOIN [TBL-MatchInfo] AS [TBL-MatchInfo_1]
ON ([TBL-MatchInfo].LNAME = [TBL-MatchInfo_1].LNAME)
AND ([TBL-MatchInfo].ADDR = [TBL-MatchInfo_1].ADDR)
AND ([TBL-MatchInfo].CITY = [TBL-MatchInfo_1].CITY)
AND ([TBL-MatchInfo].STATE = [TBL-MatchInfo_1].STATE)
WHERE [TBL-MatchInfo].ACCT<[TBL-MatchInfo_1]![ACCT]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I'm trying to create a list of possible duplicate customer accounts that can
be reviewed by CS reps. I'm starting with one table with all the customer
data. Using that I made a table (TBL-MatchData) with the fields I want to use
to determine if it may be a duplicate acct (last name, first 4 chars of
address, city, & state). Then I created the query below that gives me a list
of pairs of acct numbers that match using the criteria I mentioned.

SELECT [TBL-MatchInfo].ACCT AS ACCT1, [TBL-MatchInfo_1].ACCT AS ACCT2
FROM [TBL-MatchInfo] INNER JOIN [TBL-MatchInfo] AS [TBL-MatchInfo_1] ON
([TBL-MatchInfo].LNAME = [TBL-MatchInfo_1].LNAME) AND ([TBL-MatchInfo].ADDR =
[TBL-MatchInfo_1].ADDR) AND ([TBL-MatchInfo].CITY = [TBL-MatchInfo_1].CITY)
AND ([TBL-MatchInfo].STATE = [TBL-MatchInfo_1].STATE)
WHERE ((([TBL-MatchInfo].ACCT)<>[TBL-MatchInfo_1]![ACCT]));

The only problem is that each pair of acct numbers shows up twice, one is
just the reverse of the other.

Acct1 Acct2

A B
B A
C D
D C

How can I end up with a set of unique combinations of the acct numbers?

Acct1 Acct2
A B
C D