Hi !
There is one table tCustomers. It has following columns: ID, Name, Code...
By the mistake in this table has appeared incorrect records (duplicates).
How can I write the query to find them ?
I tried:
Select c.ID ID1,s.ID ID2, c.NAME NAME1,s.NAME NAME2, c.Code C1, s.Code C2, From tCustomers c, tCustomers s
where c.Code=s.Code and c.ID <> s.ID
But the result is not thatI expected
Hi,
If only the ID field constain duplicates, you can use the following query:
SELECT tCustomers.ID, tCustomers.Name, tCustomers.Code
FROM tCustomers
WHERE (((tCustomers.ID) In (SELECT [ID] FROM [tCustomers] GROUP BY [ID] HAVING Count(*)>1 )))
ORDER BY tCustomers.ID;
If all the fields are duplicated, use the following query:
SELECT tCustomers.ID, tCustomers.Name, tCustomers.Code, Count(tCustomers.ID) NumberOfDups
FROM tCustomers
GROUP BY tCustomers.ID, tCustomers.Name, tCustomers.Code
HAVING (((Count(tCustomers.ID))>1));
Hope this helps
