[b]Delete duplicate[/b] records

[desi5pure]

How to delete duplicate records from table/query?
Let's say, in a table there are 9 fields fld1 through fld9 and organized/grouped by least occuring to frequent occuring from fld1 - fld9.
If I want to delete all duplicate records that match on fld1, fld2, and fld3. How would I do this?

 

[randy700]

Easiest way?
Create an additional field that concatenates the 3 fields.
Use the Find Duplicates query wizard to identify the dupes.
Delete based on the results of that query.


Randy

 

[desi5pure]

Thanks Randy! I haven't used that perticular query wizard but makes sense to use that. I appreciate your response.

 

[desi5pure]

I tried but here is dilemma. Let's say I want to delete dup acct numbres. If my table looks like
ID Acct OtherFields
1 11
2 11
3 12
4 13
5 13
I want delete two dup (one 11 and one 13). The Wizard gives four records, 1, 2, 4 & 5.
1 11
2 11
4 13
5 13

Is there a way to keep distinct acct numbers and delete only duplicate records.
1 11 (One of two records 1 or two)
3 12
4 13 (One of two records id 4 or 5)?

 

[kjv1611]

1. You could use a GROUP BY clause in a query, and create a new table, then delete the original table when you know you've got done what you wanted to accomplish.

2. You could use VBA with loops running at least one recordset object, and some SQL in a string variable for deleting records as needed until it clears up.

If I remember correctly, I've used both methods.

Also, I am now using some SQL that I was shown in the MS SQL forum to do the same in one fast SQL statement - you could set it up in a query, I'm sure, or else run it from within VBA. Here's what I did, if you can follow the all the jibber-jabber:
thread183-1537897



--

"If to err is human, then I must be some kind of human!" -Me

 

[randy700]

How about this?

1. Create another table with the same field names, making the concatenated field a key field.
2. Use an append query to move the records from the original table to the new table. Because of the key field, the duplicate records will not be copied.


Randy