Solved

VBA to delete duplicate records

Posted on 2009-04-01
8
564 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

828 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