• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

delete duplicates records in access 2003

I would lile to know if in VBA code to delete the duplicate recordes to put something so that only the records which has a certain value in another field be deleted.
For example, I have 2 duplicate records by field 1 and I would like the VBA code delete the record which has N/A in field 2.
Thank you
0
marian68
Asked:
marian68
  • 7
  • 6
1 Solution
 
carsRSTCommented:
Here's the SQL (just have to modify for your tables):


delete
FROM Table1
where fld2= 'n/a'
and fld1 in (select  fld1 from Table1 group by fld1  having count(fld1) =2 )

VBA code below....


strDeleteDuplicates = "delete FROM Table1 where fld2= 'n/a' and fld1 in (select  fld1 from Table1 group by fld1  having count(fld1) =2 )"

currentproject.Connection.Execute strDeleteDuplicates 

Open in new window

0
 
marian68Author Commented:
Option Compare Database


 
 
"Sub DeleteDuplicates()
 
On Error Resume Next
 
  Dim db As DAO.Database, rst As DAO.Recordset
  Dim strDupName As String, strSaveName As String
   
  Set db = CurrentDb()
  Set rst = db.OpenRecordset("duplicatesTMREVIEW")
 
   
  If rst.BOF And rst.EOF Then
    MsgBox "No records to process"
  Else
    rst.MoveFirst
    Do Until rst.EOF
      strDupName = rst("APPLICATIO")
      If strDupName = strSaveName Then
        rst.Delete
      Else
        strSaveName = rst("APPLICATIO")
      End If
      rst.MoveNext
    Loop
     
    Set rst = Nothing
    Set db = Nothing
     
 
  End If
 
End Sub"
You can see above my VBA code that deletes my duplicate. The problem is that this code doesn't delete the right records. I would like to delete the records which have the value N/A in the field "C3". Could change the VBA code to do that?
Thank you
0
 
carsRSTCommented:
Need a little more information.

what fields are in your duplicatesTMREVIEW recordset?  Is "C3" a field and is it what the one that would hold the "n/a" value?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
marian68Author Commented:
in the duplicatesTMREVIEW query I have 63 filielld. All the duplicate records are found by the field "APPLICATIO". Yes "C3" is a field in my duplicateTMREVIEW query and I would like the the VBA code delete the duplicate records in which "C3" = "N/A"
Thank you
0
 
carsRSTCommented:
Try the code below...
"Sub DeleteDuplicates()
  
On Error Resume Next
  
  Dim db As DAO.Database, rst As DAO.Recordset
  Dim strDupName As String, strSaveName As String
    
  Set db = CurrentDb()
  Set rst = db.OpenRecordset("duplicatesTMREVIEW")
 
    
  If rst.BOF And rst.EOF Then
    MsgBox "No records to process"
  Else
    rst.MoveFirst
    Do Until rst.EOF
      strDupName = rst("APPLICATIO")
      If strDupName = strSaveName and rst("C3")= "N/A" Then
        rst.Delete
      Else
        strSaveName = rst("APPLICATIO")
      End If
      rst.MoveNext
    Loop
      
    Set rst = Nothing
    Set db = Nothing
      
  
  End If

Open in new window

0
 
marian68Author Commented:
I added at the end "End Sub" but even so it doesn't work. This code deleted 3 records randomly, 2 records "C3" = N/A and 1 record "C3" = 1. The VBA I gave you delete all my duplicate records(6) but the wrong ones.
Thank you.
0
 
carsRSTCommented:
How does strSaveName get populated the first go round?
0
 
carsRSTCommented:
Also, you might try this code below.  I adjusted it to what I know of your structure.
strDeleteDuplicates = "delete FROM duplicatesTMREVIEW where C3 = 'N/A' and [APPLICATIO] in (select  [APPLICATIO] from duplicatesTMREVIEW group by [APPLICATIO] having count([APPLICATIO]) =2 )"

currentproject.Connection.Execute strDeleteDuplicates 

Open in new window

0
 
marian68Author Commented:
Where do I have to put this code, in another module? I not good at codes.
Thank you
0
 
carsRSTCommented:
Yep, i went ahead and wrapped it up below.
SUB deleteDuplicates2()
strDeleteDuplicates = "delete FROM duplicatesTMREVIEW where C3 = 'N/A' and [APPLICATIO] in (select  [APPLICATIO] from duplicatesTMREVIEW group by [APPLICATIO] having count([APPLICATIO]) =2 )"

currentproject.Connection.Execute strDeleteDuplicates 

END SUB

Open in new window

0
 
marian68Author Commented:
It worked partially. The code didn't delete a records which has 3 times the same value in the field by which the query looked for duplicates.
Thank you
0
 
carsRSTCommented:
Cool.  I can fix that.  Try this...
SUB deleteDuplicates2()
strDeleteDuplicates = "delete FROM duplicatesTMREVIEW where C3 = 'N/A' and [APPLICATIO] in (select  [APPLICATIO] from duplicatesTMREVIEW group by [APPLICATIO] having count([APPLICATIO]) >1 )"

currentproject.Connection.Execute strDeleteDuplicates 

END SUB

Open in new window

0
 
marian68Author Commented:
It seems to work your code.
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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