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
marian68Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marian68Author Commented:
It seems to work your code.
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.