C
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
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