Need Macro To Remove Duplicate Fields

Question

I have a situation where I need a macro to remove duplicate entries in an Access database based on multiple entries of an employee ID. Basically, I need the first field "hours" to have a number in it and any additional entries for that employee ID need to be empty (See below).

End result I need:

Answer 1

Hi Christopher_Beard,

why you keep the empty records with duplicate employee ids. try to delete whole row.

you can try to use the code below.

Function DeleteDuplicateRecords(strTableName As String)
    
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    Dim varBookmark As Variant

    Set tdf = DBEngine(0)(0).TableDefs(strTableName)
    strSQL = "SELECT * FROM " & strTableName & " ORDER BY "
  
    For Each fld In tdf.Fields
        If (fld.Type <> dbMemo) And (fld.Type <> dbLongBinary) Then
            strSQL = strSQL & fld.Name & ", "
        End If
    Next fld
    
    strSQL = Left(strSQL, Len(strSQL) - 2)
    Set tdf = Nothing

    Set rst = CurrentDb.OpenRecordset(strSQL)
    Set rst2 = rst.Clone
    rst.MoveNext
    Do Until rst.EOF
        varBookmark = rst.Bookmark
        For Each fld In rst.Fields
            If fld.Value <> rst2.Fields(fld.Name).Value Then
                GoTo NextRecord
            End If
        Next fld
        rst.Delete
        GoTo SkipBookmark
NextRecord:
        rst2.Bookmark = varBookmark
SkipBookmark:
        rst.MoveNext
    Loop
End Function

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Deepak,

Unfortunately, they are not empty. Each entry has more fields than what I have shown here. I reduced the number of fields shown here for simplicity's sake, so I cannot remove any of the lines since they have data in other fields I need.

Thanks,

Christopher

Answer 3

Deepak,

if you just Google a solution and present it here, you need to honor the author by at least provide the original link and not implicitly claim it as yours. Besides, have you tested it yourself?

Original link:
http://www.databasejournal.com/features/msaccess/article.php/3077791/Delete-Duplicate-Records-From-Access-Tables.htm

---

Best regards, George

Answer 4

Well the answer is not a macro per se - but that aside really the issue is your display requirements and how flexible you are in this aspect. 

What I mean by this is that what a database will give you out of the box would be to have form/sub form or report / sub report format.  You would have the Unique record (with the hours) as the main object (form/report) with the other records (without the hours) as the sub object.  Simply make a query for each and then create an object based on each query and put them together.

If you insist on the flat display that you posted - then you would instead need to write the 2 separate queries into a temp table.

 

Answer 5

The data in this database is from an export of a third party application and they are the ones presenting us with the multi-line issue. Because of that, I think I will be stuck modifying these lines either before or after the import but I'll have to modify them one way or another. Its not like I can modify code to place the data in the right entry and field in the first place. Hopefully, that gives more insight into the situation I'm in.

Christopher

Answer 6

Not sure if this is usable, maybe you can get a new idea from it but I wrote a small sub that give you unique values. Maybe you could use the result as it is or insert in a new (temp) table?
Table:

Code:

Sub GetOneOnly()
    Dim db As Dao.Database
    Dim rs As Dao.Recordset
    Dim strSQL As String
    Dim eID As Long 'String?
    Dim Hours As Integer
    
    Set db = CurrentDb
    strSQL = "SELECT DISTINCT [Employee ID], Hours FROM Duplicates"
    Set rs = db.OpenRecordset(strSQL)
    
    Do While Not rs.EOF
      eID = rs![Employee ID]
      Hours = rs!Hours
      Debug.Print "Employee ID: " & eID & ", Hours: " & Hours
      rs.MoveNext
    Loop
End Sub

Prints:

Employee ID: 1234567, Hours: 32
Employee ID: 7123456, Hours: 40
Employee ID: 9876543, Hours: 78

---

Best regards, George

Answer 7

The source of data is not what I refer to.  I accept that you have what you have as the starting point. 

My post deals with querying this data and then presenting it as you seek.  One can go with the object/sub object display - or - write the data into a temp table for display.

If you actually seek to change the source data - that is a separate concern but the temp table would be also a vehicle for that task.

Answer 8

Hi George.B.Summers,

First I did not mentioned anywhere that I wrote this code and I did not claim for that.

the other thing I want to tell you that I did not take this code from your mentioned link.

the same code is available in many websites and I take it from another forum.

you had mentioned that I need to mentioned the name of the owner but this code available on multiple sites and I can't mentioned the name of all.

the motive is to help the op as soon as possible. if the thing is already available then we need not spend time to recreate it.

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 9

Hi Christopher_Beard,

can you tell us what is the difference in all the records?

I mean that Hours value is same for every time for 1 employee.

and we can see that you want the 1st entry of employee and for the rest you want to delete the hours.

you mentioned that we can't delete whole row as it contains other data.

so here I think that the other data are important that's why you don't want to delete so why you are deleting this one ?

do you have any issue to keep these data in the column like you are using these data in some other place and you are getting the incorrect result because of duplication of data at this place.

one another question I want to ask you that is there any possibility to get different value of hours for 1 employee?

Regards

Deepak 

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 10

Deepak,

I'm sorry you see your moderatorship this way. There are more people than I that have opinions on that.

See post https://social.msdn.microsoft.com/Forums/office/en-US/2b73f244-1e48-406f-acbc-05f53fee2346/dynamic-select-sub-query-syntax?forum=accessdev

It's rather long so search for: Good Morning Deepak

See especially #7 and #9 below that.

---

Best regards, George

Answer 11

Hi George.B.Summers,

do you know that by this kinds of posts you are distracts the threads. this type of posts never helpful to op and because of this many times op did not came back.

if we talk about another thread then the op is annoyed by his own issue and when he don't get the solution worked then they are giving the feedbacks like this.

but he did not know that he is working on a single problem for long time and we are having much then that. every time we just try to help but the people misunderstood it.

so I suggest you to post the things which help the op.

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 12

Hi Christopher_Beard,

you did not replied to the last responses given by us.

if your issue is solved now then would you like to share the solution with our community so that if any other member have same issue like you also get solution by your post.

if your issue is not solved till now then I would first recommend you to check the all suggestions and let us know it worked for you or not.

if it solves your issue mark the suggestion as an answer.

if none of the suggestion solves your issue let us know so that we can provide you further assistance.

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 13

To Everyone,

I appreciate all your help in this, but this is a very unique situation that is beyond my ability to resolve. I know I gave some very odd parameters but there is a much larger picture, too large to explain here easily, but I have to stick with those parameters such as not being able to delete any seemingly duplicate rows. I've escalated this to my boss and he's going to find one of our programmers to address the issue. Again, thank you all for your help!

Christopher

Answer 14

Hi Christopher_Beard,

Are you going to discontinue this Question?

if so then Please let us know so that we can take appropriate actions regarding this.

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 15

You can do whatever you need to. Unfortunately, there may not even be a solution to this issue.

Christopher

Answer 16

Hi Christopher_Beard,

I have an idea that can solve your issue.

First you need to add one column in your table that will contain the time.

when the records are entered in the table it will add current time in that field.

so that we can find the minimum value in time field for each employee in the table as you need to keep the value of 1st entered record for each employee.

then after we can update the value of Hour Field to " " except which record having the minimum value of time in time field.

so at the end you will get your desired result that the 1st value of Hour field for each employee will be there and any other  values in Hour field will be " ".

please try to follow the steps and test it. if you stuck in between let us know we will again try to help you to solve it.

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.