Help to write query...

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 that I 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