Solved

VBA to delete duplicate records

Posted on 2009-04-01
8
567 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses
Course of the Month9 days, 13 hours left to enroll

624 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