By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Delete Duplicate records

P: 4
I am trying to delete duplicate records in an access table using VB. Not a programmer here, but this was one of the codes that I found on the web. I made some modifications to it, but it still did not work for me.
Expand|Select|Wrap|Line Numbers
  1. Public Function DeleteDuplicate()
  2. Dim db As DAO.Database
  3. Dim qdf As QueryDef
  4. Dim strSQL As String
  5. Dim strEmail As String
  6.  
  7. dteEmail = DMax("[email]", "tbl_email")
  8.  
  9. strSQL = "Delete * FROM tbl_email" & _
  10. "WHERE (tbl_email.email)> strEmail"
  11.  
  12. Set db = CurrentDb
  13. With db
  14. Set qdf = .CreateQueryDef("tmpQuery", strSQL)
  15. DoCmd.OpenQuery "tmpQuery"
  16. .QueryDefs.Delete "tmpQuery"
  17. End With
  18. db.Close
  19. qdf.Close
  20. End Function
Any assistance is greatly appreciated.
Oct 19 '06 #1
Share this Question
Share on Google+
16 Replies


100+
P: 179
Create a query based on the table that you are checking for duplicates from. Call the query "qryDuplicates". Open the query in design view, and enter the following statement in the field that you are checking for duplicates (i.e if you are checking for duplicate serial numbers, and that field is called "serialnumber"), enter this statement in criteria under the field "serialnumber":

In (SELECT [serialnumber] FROM [tablename] As Tmp GROUP BY [serialnumber] HAVING Count(*)>1 )

After doing that, create an APPEND query, and base it on the qryDuplicates query. When you run the APPEND query, it should delete all the duplicates in that table.

Hope this helps.
comteck
Oct 19 '06 #2

Expert 5K+
P: 8,434
I don't get it. How can this delete anything? Won't it just append to somewhere the complete list of records which have duplicate serial numbers?
Oct 20 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
dteEmail = DMax("[email]", "tbl_email")

should be

strEmail = DMax("[email]", "tbl_email")

db.Close ' never close the database just set it to nothing and close the query first. i.e.

qdf.Close
Set qdf = Nothing
Set db = Nothing
Oct 20 '06 #4

P: 4
Apologies. I wasn't not clear on my original post.

I would like to delete duplicate records (email field), but keep at least one record. I have revised the script based on the replies that I have recieved, but I am still not able to get the script to run correctly.

Any help is appreciated.

Thanks.
Oct 20 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
What is the value in email field. As you are using DMax I assume it's a number or date. Can you confirm?

If it's a date, are you just trying to create a list of emails by the last date sent by deleting all other emails.
Otherwise what is the criteria?

Apologies. I wasn't not clear on my original post.

I would like to delete duplicate records (email field), but keep at least one record. I have revised the script based on the replies that I have recieved, but I am still not able to get the script to run correctly.

Any help is appreciated.

Thanks.
Oct 20 '06 #6

P: 4
The values of DMax are alphanumeric.

I am trying to delete duplicated (SMTP - email) since it is my only unique identifier available. It is a text field.

Thanks.

<What is the value in email field. As you are using DMax I assume it's a number or date. Can you confirm?

If it's a date, are you just trying to create a list of emails by the last date sent by deleting all other emails.
Otherwise what is the criteria>
Oct 20 '06 #7

100+
P: 179
I don't get it. How can this delete anything? Won't it just append to somewhere the complete list of records which have duplicate serial numbers?
Sorry Killer.... it did work for me. Might be a different case here. However, not everybody is correct in their solutions that they offer on here. Normally however, people are not rude about it.
Oct 20 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
DMax gets the Maximum Value you can't do that with an AlphaNumeric field.

BACKUP the table first!!

Try the following. First add a field to the table called temp with a true/false datatype. You can delete it later.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function DeleteDuplicate()
  3. Dim db As DAO.Database
  4. Dim rs As RecordSet
  5. Dim strEmail As String
  6.  
  7. Set db = CurrentDb
  8. Set rs = db.OpenRecordset("tbl_email")
  9.  
  10. StartFile:
  11.  
  12. rs.MoveFirst
  13. Do Until rs!temp = False
  14.   If not rs.EOF then
  15.     rs.MoveNext
  16.   Else
  17.     GoTo EndFile
  18.   End If
  19. Loop
  20.  
  21. strEmail = rs!email
  22. rs!temp = True
  23. rs.MoveNext
  24.  
  25. Do Until rs.EOF
  26.   If rs!email = strEmail Then
  27.     rs.Delete
  28.   End If
  29.   rs.MoveNext
  30. Loop
  31.  
  32. GoTo StartFile
  33.  
  34. EndFile:
  35.  
  36. rs.close
  37. set rs = Nothing
  38. set db = Nothing
  39.  
  40. End Function
  41.  
  42.  
Oct 20 '06 #9

NeoPa
Expert Mod 15k+
P: 31,494
M McCarthy - how can you do this to me?
I was working on a little routine for this thread when you posted a perfectly good answer.
Not cool ;-)

Anyway, as I've done it I might as well include it here.

This code does assume a table in the local db but it can be tweaked or enhanced.
For instance, if the one to be kept must have the lowest Primary Key value then it could be changed to do that.

Expand|Select|Wrap|Line Numbers
  1. 'DelDups Removes duplicate records in strTable matched on strField
  2. Public Sub DelDups(strTable As String, strField As String)
  3.     Dim strSQL As String, varLastVal As Variant
  4.  
  5.     'Recordset must be full table but sorted by the field we're checking
  6.     strSQL = "SELECT * FROM [" & strTable & "] ORDER BY [" & strField & "]"
  7.     With CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
  8.         varLastVal = Null
  9.         'For each record, check against previous value in strField
  10.         'If same then this is a duplicate so can be removed
  11.         Do Until .EOF
  12.             If .Fields(strField) = varLastVal Then
  13.                 Call .Delete
  14.             Else
  15.                 varLastVal = .Fields(strField)
  16.             End If
  17.             Call .MoveNext
  18.         Loop
  19.     'Ending the 'With' releases the Recordset
  20.     End With
  21. End Sub
Hope it helps.
Oct 20 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi NeoPa

I hate it when that happens. You get all excited when you get it working then post it to find someone got in before you. Happens to me all the time.

Mary
Oct 20 '06 #11

Expert 5K+
P: 8,434
Sorry Killer.... it did work for me. Might be a different case here. However, not everybody is correct in their solutions that they offer on here. Normally however, people are not rude about it.
Apologies if I sounded a bit rude - it wasn't intended that way.
Oct 20 '06 #12

P: 44
hi,

Step 1: Use make a table query and name it e.g. new table
Step 2: Delete all records of this new table (as it contains all records)
Step 3: After deletion of records, set primary key in new table
Step 4: Now use append query. You will see that only unique records will be appended in new table. Good luck
Oct 23 '06 #13

P: 4
MMcCarthy,

Sorry I haven't responded sooner.

Your coding works like a charm. I appreciate everyone's assistance.

Thanks!
Oct 24 '06 #14

P: 3
MMcCarthy,

Sorry I haven't responded sooner.

Your coding works like a charm. I appreciate everyone's assistance.

Thanks!

Hmm.... this code dosn't seem to work for me. I have added a temp field to my table to try this out, but when i try to run it i get "Update or CancelUpdate without AddNew or Edit" the error pops up on rst!temp = true.
Jul 14 '08 #15

NeoPa
Expert Mod 15k+
P: 31,494
I think you're better off creating your own thread for this.

It's perfectly acceptable to include a link to this one if you feel that would help - but you must explain your position clearly anyway.

Welcome to Bytes :)
Jul 15 '08 #16

NeoPa
Expert Mod 15k+
P: 31,494
A hijack post has been split off into Delete Duplicated Rows. Anyone interested can find it there.
Nov 15 '09 #17

Post your reply

Sign in to post your reply or Sign up for a free account.