Solved Delete Duplicate Records by Query

[Sarah.M]

Hi, Plz note, My VBA is disabled for security reason.
I can use only Expression builder, Macros, Queries, Tables, Forms and Reports only.
------------------------------------------------
I have 2 Tables
Tables 1 Appended to Table 2 and I got duplicate records,
I use Wizard to find the duplicate, but I do know how to delete them
any advice how to delete duplicates records plz?

 

[arnelgp]

you can try:

DELETE *
FROM [Table 2]
WHERE ID <> (SELECT Min(ID) AS MinOfID FROM [Table 2] AS Dupe
WHERE (Dupe.FieldWithDuplicate = [Table 2].FieldWithDuplicate));

 

[oleronesoftwares]

Lets assume you have the following tables.

1. Calculate // this is the initial table
2. Duplicate // this is the duplicate table

And the two tables have the following fields in each of them
1.ID (pkey)
2.qty
3. price
4. total

Enter the following delete query
DELETE duplicate.*, duplicate.qty, duplicate.price, duplicate.total
FROM calculate INNER JOIN duplicate ON calculate.ID = duplicate.ID
WHERE (((duplicate.qty)=[calculate].[qty]) AND ((duplicate.price)=[calculate].[price]));

 

[Pat Hartman]

Start with a backup. Zip the backup to prevent accidents.

1. Turn off the Name auto correct feature. It is dangerous at best and it will interfere with the following process. You can turn it back on when we're done if you want it on.
2. Rename the original table to originalName_OLD
3. Create a new table named originalName
4. Put the proper unique indexes on the table to prevent duplicates.
5. Run an append query to copy the rows from originalName_OLD to originalName

If you want to control the "duplicate" that gets saved, you can use a sort in the append query to get the record you want to appear first assuming there is something meaningful to sort on.

Only the unique records will be appended. The Unique index will cause the duplicates to be discarded. You will get a message at the end explaining that some records were not appended.