Improve company productivity with a Business Account.Sign Up

x
?
Solved

VBA to delete duplicate records

Posted on 2009-04-01
8
Medium Priority
?
575 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
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…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

606 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