Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 901
  • Last Modified:

find duplicate records in MS Access

Could anyone can tell me how to find all the duplicate records in a table and delete the duplicate? Thanks a lot!
0
omanca
Asked:
omanca
1 Solution
 
Ryan ChongCommented:
Try something like:

Delete  from table1 where id in

(

Select ID from Table1 Where [referkey] in (
SELECT [referkey] FROM [Table1] As Tmp GROUP BY [referkey] HAVING Count(*)>1
) And iD not in (        

SELECT  Min(ID) AS MinKey from table1 Group by TAble1.referkey

)

)

?
0
 
peter57rCommented:
It depends on how many records and duplicates you think you have. and what you want to do in the future.

If you start a new query you have a Find Duplicates query wizard which will show you all occurrences of the duplicated records.
If the numbers are small you could then manually delete the unwanted ones.

If you have too many for that then you you can use a trick.

Copy the table containing the duplicates. (Right-click the table name and select copy)
Paste and select Structure only.  Use a name of your choice.

Now modify the design of the copy to prevent any duplicates occuring.
To do this set the primary key or create a unique index based on the fields which  identify the duplication.

Now create an append query which appends records from the original table to the new table.

When you run this query all duplicates will be rejected and you can rename the old and new tables.

Pete




0
 
omancaAuthor Commented:
Thanks Pete,
It was good enough to know that there is a "Find Duplicates query wizard"

Omanca
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now