MS Access Query - To get customer ID which has different names in the different field

Question

I have two column one is Address ID and another one is Customer Name. below table is for reference

0608	Internet company
3623	Authority
3623	Authority
5478	Group Holdings (Pty) Ltd.
5478	Shankara
5478	Group Holdings (Pty) Ltd.

In MS access I want to retrieve 5478 Shankara similar records.

Please help to write a MS access DB query to get records with same Address ID having different Customer names.

Answer 1

You can use a subquery:

Select * 
From YourTable
Where [Address ID] In 
    (Select [Address ID] 
    From YourTable
    Group By [Address ID] 
    Having Count(*) > 1)

Answer 2

You can select the Ids related to different names by using a sub-SELECT with a DISTINCT keyword. This eliminates duplicates where the name is the same for one Id.

SELECT CustId
FROM (SELECT DISTINCT CustId, Name FROM mytable)
GROUP BY CustId
HAVING Count(*) > 1

This returns only the corresponding CustId once. If you want all fields of these records, you can use the above query as nested SELECT

SELECT * FROM mytable
WHERE CustId IN (
    SELECT CustId
    FROM (SELECT DISTINCT CustId, Name FROM mytable)
    GROUP BY CustId
    HAVING Count(*) > 1
)

If you want all combinations only once, you can add DISTINCT again:

SELECT DISTINCT CustId, Name FROM mytable
WHERE CustId IN (
    SELECT CustId
    FROM (SELECT DISTINCT CustId, Name FROM mytable)
    GROUP BY CustId
    HAVING Count(*) > 1
)

And there are probably other variants. It depends on the exact specification (which is not very clear to me).