Find Duplicates that don't match single field (1 Viewer)

[SteveClarkson]

Hello all,

I am sure this must be simple, but I can't get my head around it.

I have a query that looks at client bank account numbers, and finds duplicates.

There are a LOT of duplicates, because we have several accounts for the same person.

What I would like to do is find all the records where the account number and sort code match, but where the account NAME is different.

I am trying to find any records where the same account number is being used on more than one account.

All the fields are in a single table.

This is what I have so far, which just finds duplicate sort codes and account numbers;

SELECT CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE, CPL_ACCOUNT.BANK_NAME
FROM CPL_ACCOUNT
WHERE (((CPL_ACCOUNT.ACCOUNT_NUMBER) In (SELECT [ACCOUNT_NUMBER] FROM [CPL_ACCOUNT] As Tmp GROUP BY [ACCOUNT_NUMBER],[SORT_CODE] HAVING Count(*)>1  And [SORT_CODE] = [CPL_ACCOUNT].[SORT_CODE])))
ORDER BY CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE;

Thank you!

 

[ByteMyzer]

Try the following:

SELECT C1.ACCOUNT_NUMBER, C1.SORT_CODE, C1.BANK_NAME
FROM CPL_ACCOUNT AS C1
WHERE EXISTS
 (SELECT C2.*
  FROM CPL_ACCOUNT AS C2
  WHERE C2.ACCOUNT_NUMBER=C1.ACCOUNT_NUMBER
  AND C2.SORT_CODE=C1.SORT_CODE
  AND C2.BANK_NAME<>C1.BANK_NAME
 )
;

See if this solution works for you.