Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

separating records

Posted on 2011-03-11
54
Medium Priority
?
390 Views
Last Modified: 2012-08-14
Hi all,

Grateful for some assistance.

I have accidently populated an Access 2003 table with a complete set of identical records that already exist. In other words, the table has two of the same record in the table.

Is there a vba script that could separate the two records or delete a copy of the identical record.

So far I have converted the Access table into Excel and then individually delete the records one by one. Then importing it back.

I'm wondering if there is a simpler method, using VBA or MSAccess

thanks
0
Comment
Question by:PipMic
[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
  • 29
  • 9
  • 6
  • +4
54 Comments
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 200 total points
ID: 35110603
Considering that you already exported the table into excel, you may use the Remove Duplicates features in Excel. Then after the duplicates are removed you can import it in Access.

Sincerely,
Ed
remove-duplicates.jpg
0
 

Author Comment

by:PipMic
ID: 35111234
Hi,

Thanks...for the info...was wondering now whether it could be done in Msaccess itself . It would mean not having to export, etc.

Thanks again
0
 

Author Comment

by:PipMic
ID: 35111260
Hi Mindsuperb,

tried and worked fine...

Would prefer VBA in Msaccess though....so far ur ahead on the points.

Ciao
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 200 total points
ID: 35111281
Below is part of the article on this link

http://office.microsoft.com/en-us/access-help/find-eliminate-or-hide-duplicate-records-in-access-HA001034558.aspx#BM15

In that article, the part relevent to your question starts at the phrase:

"I want to delete records that are duplicates of others"

Sincerely,
Ed


<lengthy quoted passage from that article deleted by modus_operandi>
0
 

Author Comment

by:PipMic
ID: 35112104
Many thanks,

Still grateful if someone could provide me with some vba code that i could adapt to my problem.

Basically lookup all the records whic are identical in terms of content and then remove the the copy.

Thanks
0
 

Author Comment

by:PipMic
ID: 35121277
Hi,

I have made a sample database to test the above procedure but no luck.

I'm attaching it in case someone can ascertain where i've gone wrong.
 it has been done using Access 2003

Thanks duplicatedelete.mdb
0
 
LVL 70

Expert Comment

by:KCTS
ID: 35123158
Why not use the find duplicates query in Access ?
0
 

Author Comment

by:PipMic
ID: 35123261
Hi KCTS,

thanks for you comment but how do you do that? please note i'm using A2003

0
 
LVL 70

Expert Comment

by:KCTS
ID: 35123843
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 35123928
Rough code below.
Public Function DeleteDupeRecord()

Dim PK As Integer

Dim DB As Database
Dim RS As Recordset
Dim SQL As String

'Openeing the table to read/write to
SQL = "SELECT * " & _
        "FROM MyTable " & _
        "ORDER BY PK"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)    '<-- Change to your tablename

PK = 0

RS.MoveFirst

Do Until RS.EOF = True

    If PK <> RS.PK Then
        RS.Delete
        PK = RS.PK
    Else
        RS.MoveNext
    End If
    
Loop

Set RS = Nothing
Set DB = Nothing

End Function

Open in new window

0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 100 total points
ID: 35124717
Back up database.
Make a copy of your table. Structure only.
In new table, set a unique composite index, of all fields, or at least enough to comprise uniqueness.
Create append query "Select * from oldTable into NewTable".
You will be warned that n records will not be appended. (The copies.)
Delete original table. Rename new table to original table's name.
0
 

Author Comment

by:PipMic
ID: 35128263
Thanks all for these latest hints.

Will work on them today(hopefully) and get back to you.

0
 

Author Comment

by:PipMic
ID: 35129697
Hi again,

Tried the Tech Republic process and still didnt work. I get the same results as my sample database which I have attached previously.

I'm wondering whether its to do with my table structure!!??

thanks for any comments.
0
 

Author Comment

by:PipMic
ID: 35129889
Hi Jimpen,

Tried your code.

I think I may have done something wrong. I'm not too good on the vba though.

I'm attaching my simple test database for your perusal.

Please advise.
duplicatedelete3.mdb
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 35130166
Try this:
Private Sub Command1_Click()

Dim ColA As String
Dim ColB As String
Dim ColC As String

Dim FirstPass As Boolean

Dim DB As Database
Dim RS As Recordset
Dim SQL As String

'Openeing the table to read/write to
SQL = "SELECT * " & _
        "FROM MyTable " & _
        "ORDER BY A, B, C"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(MyTable)
'Set RS = DB.OpenRecordset(SQL)    '<-- Change to your tablename

FirstPass = True
ColA
ColB
ColC
RS.MoveFirst
With RS
    ColA = .A
    ColB = .B
    ColC = .C
End With

Do Until RS.EOF = True

    If FirstPass = False And _
        ColA = RS.A And _
        ColB = RS.B And _
        ColC = RS.C Then
            RS.Delete
            With RS
                ColA = .A
                ColB = .B
                ColC = .C
            End With
    Else
        If FirstPass = True Then FirstPass = False
        RS.MoveNext
    End If
    
Loop

Set RS = Nothing
Set DB = Nothing

End Sub

Open in new window

0
 

Author Comment

by:PipMic
ID: 35130823
hi,

tried the code, i get the following.

got the following errors.


duplicate-error.doc
duplicatedelete3.mdb
0
 

Author Comment

by:PipMic
ID: 35130853
Hi KCTS,

Grateful if you could look my database.

Can't figure out why it doesnt work?

Thanks


duplicatedelete2.mdb
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 35131676
PipMic,
Did you try what I suggested?
0
 

Author Comment

by:PipMic
ID: 35131739
Hi Jerryb30

Yes your procedure is very much the same as that being suggested previously. But it wont work!! I wonder if it has to do with my table structure. Pls see my comments above. Thanks again.

Please checkout my databases attached previously.

Thanks again.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 35132714
I left in some extraneous text.
Private Sub Command1_Click()

Dim ColA As String
Dim ColB As String
Dim ColC As String

Dim FirstPass As Boolean

Dim DB As Database
Dim RS As Recordset
Dim SQL As String

'Openeing the table to read/write to
SQL = "SELECT * " & _
        "FROM MyTable " & _
        "ORDER BY A, B, C"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(MyTable)
'Set RS = DB.OpenRecordset(SQL)    '<-- Change to your tablename

FirstPass = True

RS.MoveFirst
With RS
    ColA = .A
    ColB = .B
    ColC = .C
End With

Do Until RS.EOF = True

    If FirstPass = False And _
        ColA = RS.A And _
        ColB = RS.B And _
        ColC = RS.C Then
            RS.Delete
            With RS
                ColA = .A
                ColB = .B
                ColC = .C
            End With
    Else
        If FirstPass = True Then FirstPass = False
        RS.MoveNext
    End If
    
Loop

Set RS = Nothing
Set DB = Nothing

End Sub

Open in new window

0
 

Author Comment

by:PipMic
ID: 35133131
Hi jimpen,

I still get a compile error.  "Method or Data member not found"

It stops on line 26.

Is this being done with MSaccess 2003 in mind i.e. VBA?

Thanks for your time.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 35133336
That's what I get for spinning code quickly.
Private Sub Command1a_Click()

Dim ColA As String
Dim ColB As String
Dim ColC As String

Dim FirstPass As Boolean

Dim DB As Database
Dim RS As Recordset
Dim SQL As String

'Openeing the table to read/write to
SQL = "SELECT * " & _
        "FROM MyTable " & _
        "ORDER BY A, B, C"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)    '<-- Change to your tablename

FirstPass = True

RS.MoveFirst
ColA = RS!A
ColB = RS!B
ColC = RS!C


Do Until RS.EOF = True

    If FirstPass = False And _
        ColA = RS!A And _
        ColB = RS!B And _
        ColC = RS!C Then
            RS.Delete
            ColA = RS!A
            ColB = RS!B
            ColC = RS!C
    Else
        If FirstPass = True Then FirstPass = False
        RS.MoveNext
    End If
    
Loop

Set RS = Nothing
Set DB = Nothing

End Sub

Open in new window

0
 

Author Comment

by:PipMic
ID: 35135852
Hi Jimpen,,

I'm still getting an error. I'm attaching a screendump of the error.

Thanks for yopur continued support.
DuplicateRecord-error-using-VBA.doc
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 35138783
It should be  
Set RS = DB.OpenRecordset(SQL)

Open in new window

Not MyTable. If you want to open the table directly you would surround it with quotes.
 
Set RS = DB.OpenRecordset("MyTable")

Open in new window


The problem with that is natural sort order would not necessarily get the records one after the other to delete it.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 35138805
Oh, and you can just save the images directly as a jpg with paint and attach them. That saves people from having to down load the word doc.
0
 

Author Comment

by:PipMic
ID: 35138991
Hi,

Code produces no errors, but it wont delete the duplicate record :(

I'm sure you're nearly there!

I have tried understanding it and so far I think I can follow , but I cant figure out why it wont delete.

Thanks
0
 

Author Comment

by:PipMic
ID: 35139029
Hi again,

I changed the line to read....("MyTable")

and the code worked. It actually deleted the duplicate record but I get an error message that says...

Runtime error 3167,         record is deleted.

Any clues?
0
 

Author Comment

by:PipMic
ID: 35139076
Hi,

I'm sending the screen dump for the error 3167.

Thanks again.
runtime-error-3167.bmp
0
 

Author Comment

by:PipMic
ID: 35139208
Hi...

Sorry to bother you again, but the moment i enter new data the pocedure won't work.

Thanks
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 35140469
Because opening the table directly will put it in its natural sort order. Which has the latest records on the bottom until the table is reindexed.  That is why it is important to use the SQL with the order by clause ("ORDER BY A, B, C"). That forces the sort.
 
'Openeing the table to read/write to
SQL = "SELECT * " & _
        "FROM MyTable " & _
        "ORDER BY A, B, C"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)    '<-- Change to your tablename

Open in new window


Runtime error 3167,         record is deleted.
 
'Openeing the table to read/write to
SQL = "SELECT * " & _
        "FROM MyTable " & _
        "ORDER BY A, B, C"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)    '<-- Change to your tablename

Open in new window

If RS.EOF = False Then
            rs.movenext
            ColA = RS!A
            ColB = RS!B
            ColC = RS!C
       End if

Open in new window

0
 

Author Comment

by:PipMic
ID: 35141576
Hi jimpen,

Thanks for amended code, but what should i replace?
i.e the following code should replace  or should I add to the existing code.

If RS.EOF = False Then
            rs.movenext
            ColA = RS!A
            ColB = RS!B
            ColC = RS!C
       End if


Thanks
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 100 total points
ID: 35141916
It would be replace in the area that is highlighted.
0
 

Author Comment

by:PipMic
ID: 35142400
My apologies jimpen,

 this is the code i have so far, that you have kindly offered. What does the amendment you suggest fit in or replace. I have tried different things but none work.

Private Sub Command2_Click()

Dim ColA As String
Dim ColB As String
Dim ColC As String

Dim FirstPass As Boolean

Dim DB As Database
Dim RS As Recordset
Dim SQL As String

'Openeing the table to read/write to
SQL = "SELECT * " & _
        "FROM MyTable " & _
        "ORDER BY a, b, c"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("MyTable")
   
FirstPass = True

RS.MoveFirst
ColA = RS!A
ColB = RS!B
ColC = RS!C


Do Until RS.EOF = True

    If FirstPass = False And _
        ColA = RS!A And _
        ColB = RS!B And _
        ColC = RS!C Then
            RS.Delete
            ColA = RS!A
            ColB = RS!B
            ColC = RS!C
    Else
        If FirstPass = True Then FirstPass = False
        RS.MoveNext
    End If
   
Loop

Set RS = Nothing
Set DB = Nothing


End Sub
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 35143841
Try this on.
Private Sub Command2_Click()

Dim ColA As String
Dim ColB As String
Dim ColC As String

Dim FirstPass As Boolean

Dim DB As Database
Dim RS As Recordset
Dim SQL As String

'Opening the table/query to read/write to
SQL = "SELECT * " & _
        "FROM MyTable " & _
        "ORDER BY a, b, c"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("MyTable")

'Setting the first pass thru   
FirstPass = True

'move to the first record
RS.MoveFirst

'reading the variables in for the first run through
ColA = RS!A
ColB = RS!B
ColC = RS!C

'loop until you run out of records
Do Until RS.EOF = True

    'If the first run through is done _
     and the rest match delete it.
    If FirstPass = False And _
        ColA = RS!A And _
        ColB = RS!B And _
        ColC = RS!C Then
            RS.Delete
         'if not the last record reload the next set.
           If RS.EOF = False Then
               rs.movenext
               ColA = RS!A
               ColB = RS!B
               ColC = RS!C
           End if
   Else
     'just move onto the next one
        If FirstPass = True Then FirstPass = False
        RS.MoveNext
    End If
   
Loop

'close out everything
Set RS = Nothing
Set DB = Nothing

End Sub

Open in new window

0
 

Author Comment

by:PipMic
ID: 35146424
Hi,

Still no joy,

I'm attaching the database for your perusal. I get no run time errors but the duplicate record is not deleted.

Thanks again for your kind assistance.
duplicatedelete4.mdb
0
 
LVL 28

Expert Comment

by:burrcm
ID: 35154740
Hi.

How I do it. First update your table with a field concatenating the existing fields so the duplicates are listed without doubt. dup:a&b&c. Now copy the structure only of this table to a new table. Set the dup column as primary key. Run an append query on the orig table to the new. Access will fail to append the duplicates as this will violate the primary key. See attached.

Chris B
duplicatedelete5.mdb
0
 

Author Comment

by:PipMic
ID: 35156189
Hi burrcm,

Thanks, thats a very clever way. Though in this case its three simple columns with limited characters.

It still doesnt explain why the above mentioned methods will not work. I would have expected that there is a way of deleting the records without concatenating. The Microsoft way makes no mention of it!!

However this method I'll keep in mind. Thanks once again.

I'm still hopefull that jimpen's code can be fixed and made to work.

Thanks again
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 100 total points
ID: 35403552
1. Add an autonumber column to the table
2. run this query

DELETE *
FROM MyTable
WHERE MyTable.ID > (
Select  Min(MT.ID) 
From MyTable As MT 
Where (MyTable.c=MT.c) AND (MyTable.b=MT.b) AND (MyTable.a=MT.a) )

Open in new window


3. remove the autonumber field.

Note: in this example, the autonumber field is named ID
0
 

Author Comment

by:PipMic
ID: 35412045
Hi,

Thanks.

But I cant add the autonumber field. I am looking for someone to correct the code that I have attached above, i.e the code that jimpen supplied. See Deleteduplicate4.mdb (attached above).

Pipmic
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35412066
why can't you add an autonumber field?  Is there already an autonumber field?
0
 

Author Comment

by:PipMic
ID: 35414091
Hi,

Thanks for your reply.

The thing is, I've inherited this database with a table which has accidentally been populated with records that already exist.

The whole exercise that I have set up in this thread, is one that more or less mimics the real one. Hence I have omitted PKs and other database rules because that is what I have. Therefore I simply simply want to find a coherent piece of VBA code that will save me the trouble of sorting the records and then deleting  the duplicate records that I see.

Obviously I would be working on a copy to start of with so that I would first understand the code before I embark on the real thing.

I completely understand everyone's concerns that maybe the database is structured improperly or whether additional fields have to be included but my main concern is deleting the duplicates.

Jimpens code as set up above is almost there, I think, but somehow it fails. I am sure it requires some tweaking.

Basically what I need is code which will sort the table and then compare the entire contents of one record with the one below it. If it is the same, ie. all the fields are the same, then one of the duplicates should be deleted.


Regards
pipmic
0
 

Author Comment

by:PipMic
ID: 35414964
Dear All,

I have found an interim piece of VBA code which so far does the trick albeit creating a separate table

It can be found in

http://www.mrexcel.com/forum/showthread.php?t=291847

Will test it now and inform in due course
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35415061
@pipmic

>>The whole exercise that I have set up in this thread...
Is this coursework/homework?

Since this is your table and your database, you can certainly add an autonumber field.  If you don't want/need the autonumber field after the duplicates have been removed, you can delete the autonumber field.
 
>>...deleting  the duplicate records...
That is your stated goal.  That is the goal that the experts have been working toward.  VBA code is not required.

=======
For future readers:  You do not need VBA code to remove duplicate records.  As long as you have an autonumber field, a delete query will remove the duplicates.

You can also run a make-table query, using Select Distinct to create a new table without duplicates.  Then you can rename the current (duplicate-containing) production table and the new (duplicate-free) table as the production name.
0
 

Author Comment

by:PipMic
ID: 35419731
Hi,

No this is not homework at all. I'm 47 and certainly not in school! I was simply adapting a sample database to the problem I inherited.

I am obviously not an expert with Access and that is why I constantly approach Experts Exchange (to whom I am subscibed to) to seek advice when I find myself in a spot of bother. I don't expect to be criticised whenever I ask something that "shouldn't" be asked or if the database is not constructed appropriately. I  do not mind constructive criticsm at all but if the solution I am seeking is not possible then someone should tell me.

Regarding the autonumber, yes you're correct but  I was simply curious enough to know whether I could adapt some VBA code to correct the problem.

Also you are correct in that VBA is not required but in my initial comment I did say whether there was simpler solution using VBA. I had hoped for a more "nerdy" and a more program like method to delete the duplicate records at the touch of a button. All through the thread it is evident that I was seeking a VBA solution!

I make no apologies for the questions I am asking. If there is a restriction to what can be said or not then it is up to the moderator to inform me.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35420047
I thought you had presented coursework when you referred to this as an exercise.

I spoke up because your insistence on a VBA solution.  Moreover, the fact that you ignored/rejected several non-VBA solutions to deduplicate your data seemed suspect.  We're not restricting your questions, but please understand that experts might want to know why you aren't following up on their suggested solutions.

In general, you want to use the capabilities of the database engine whenever you can.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35429727
One of my Access associates reminded me of another way of removing duplicates.

1. Copy/paste the table structure (no data)
2. Add a unique index on the three columns (multi-column index)
3. Append the data into the new table (note: only the first instance of the column valued rows will be appended)

At this point, you can either rename the two tables or delete the rows in the original table and then appending the data from the new table to the original table.
0
 

Author Comment

by:PipMic
ID: 35704897
Hi,

I received assistance, and the code I received was:

Private Sub Command0_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("TABLE NAME")

sSQL = "SELECT DISTINCT * "
sSQL = sSQL + "INTO [MyTemp] "
sSQL = sSQL + "FROM [TABLE NAME]"

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

MsgBox " A Table without duplicates has been created as MyTemp", vbOKOnly, "Created"

Set rst = Nothing
Set db = Nothing

   
End Sub


This worked perfectly and it was what I was trying to do. I hope this helps others who may be in need of deleting duplicates.

0
 

Author Comment

by:PipMic
ID: 35704905
Hi,

forgot ot mention that I had to make slight amendments from the original because I was working in A97.

However because everyone was ok I'll be splitting the points here.

Thanks
0
 

Author Comment

by:PipMic
ID: 35704930
how can apportion points ???

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35705055
click the Accept Multiple Solutions link on the best comment.  You will be able to allot points to multiple comments.
0
 

Author Closing Comment

by:PipMic
ID: 35705823
Had to apply Excel code for it!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

721 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