Finding duplicates with no wizard

  • Thread starter Thread starter C Tate
  • Start date Start date
C

C Tate

Can anyone tell me how I would go about finding duplicates in a table
without using the 'find duplicates' wizard (it isn't installed on my machine
at work!)?

I have a table and wish to find all the 'ClientID' duplicates as they can
appear more than once.
 
You could create a totals query, with ClientID pulled in
twice. The first one would use group by, the second one
would use count in the totals field. Criteria would be >1
for the counted ClientID. This will show you all
ClientIDs that appear multiple times. If you wanted to
display the actual rows from the table, create another
query that joins this query to your table by clientID.
Then, every row that appears multiple times will be
displayed.

Hope this helps.
 
SELECT ClientID, OtherFields
FROM YourTable
WHERE ClientID IN
(SELECT T.ClientID
FROM YourTable as T
GROUP BY T.ClientID
HAVING Count(*) > 1)

If you are doing this in the query grid, build your normal select query in the
criteria under the ClientID field, you will enter

Criteria: IN (SELECT T.[ClientID] FROM [YourTable] as T GROUP BY T.[ClientID]
HAVING Count(*) > 1)

Replace the field name ClientID with your field name and table name [YourTable]
with you actual field and table name
 
Back
Top