Solved

VBA to delete duplicate records

Posted on 2009-04-01
8
562 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 24038510
what happened with the codes in your original post?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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 119

Expert Comment

by:Rey Obrero
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now