Solved

separating records

Posted on 2011-03-11
54
380 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
  • 29
  • 9
  • 6
  • +4
54 Comments
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 100 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
 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 100 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 50 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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 50 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 45

Assisted Solution

by:aikimark
aikimark earned 50 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 45

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 45

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 45

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 45

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 45

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

747 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

12 Experts available now in Live!

Get 1:1 Help Now