Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA to delete duplicate records

Posted on 2009-04-01
8
Medium Priority
?
572 Views
Last Modified: 2013-11-29
Can any One check this code.
I have a table called Data_complete with many duplicate records(all fields exactly the same).
I am trying to delete duplicate records thourgh this query. But this isnt working fine..
Can anyone please help in this code ?
Private Sub Command2_Click()
Dim strSQL As String
    DoCmd.SetWarnings False
    DoCmd.Rename "Data_complete_OLD", acTable, "Data_complete"
    strSQL = "SELECT DISTINCT * INTO Data_complete FROM Data_complete_OLD" & vbCrLf
    CurrentDb.Execute strSQL, dbFailOnError
    DoCmd.DeleteObject acTable, "Data_complete_OLD"
    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
    If InStr(tdf.Name, "error") > 0 Then
    DoCmd.DeleteObject acTable, tdf.Name
    End If
    Next tdf
    DoCmd.SetWarnings True
    MsgBox "done"
End Sub

Open in new window

0
Comment
Question by:siva_iaf
  • 4
  • 4
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24038318
try your codes without this line

  'DoCmd.Rename "Data_complete_OLD", acTable, "Data_complete"
0
 

Author Comment

by:siva_iaf
ID: 24038469
Ok i will modify my question now...
In a table called tblBaseData I have records with 65 Columns nearly. Three of these records are called Ticket, LegNumber and AmendLevel and Pay Date.
I want to delete a complete record for Ticket with same AmendLevel and LegNumber.

Example:
ticket no            Leg No           AmendLevel             PayDAte
981                     0                   1                                25 Mar
981                     0                    1                                26 MAr
323                     1                    3                                18 Mar
323                     2                    1                                 18 Mar

In this example the records for ticket number 981 is not unique but since the leg no and amendlevel is same in both the records i want to delete one of the record with pay date less. That means the first record for 981 should get deleted. Can you please help me with a VBA code for this please...
Regards
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24038510
what happened with the codes in your original post?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 24038529

try this query

select [ticket no],[Leg No],AmendLevel,Max([PayDAte])
from tableX
group by [ticket no],[Leg No],AmendLevel
0
 

Author Comment

by:siva_iaf
ID: 24038531
Actaully i tested it post posting the question and found that it was working fine......
I just checked that the PayDAte was differing in two records and hence it was retaining 2 records..
So No probs with the first query.

In order to overcome that problems, i am asking for the second question.
Could you kindly help.
Reg
0
 

Author Comment

by:siva_iaf
ID: 24038671
But this will select only tickt no leg no and amend level ...but i want to extract all the 60 fields in
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24038840
then why didn't you mention that you more than the fields you posted..
0
 

Author Comment

by:siva_iaf
ID: 24038874
Sir
Kindly refer my post ID: 24038469.
i had mentioned... 65 Columns nearly....Kinldy help..

Regards
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question