none
Need Macro To Remove Duplicate Fields RRS feed

  • 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:

    Wednesday, June 15, 2016 7:12 PM

All replies

  • 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.

    Thursday, June 16, 2016 5:23 AM
    Moderator
  • 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

    Thursday, June 16, 2016 12:22 PM
  • 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

    Thursday, June 16, 2016 12:44 PM
  • 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.

     

    Thursday, June 16, 2016 12:46 PM
  • 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

    Thursday, June 16, 2016 12:59 PM
  • 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


    Thursday, June 16, 2016 1:11 PM
  • 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.

    Thursday, June 16, 2016 1:27 PM
  • 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.

    Friday, June 17, 2016 5:17 AM
    Moderator
  • 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.

    Friday, June 17, 2016 6:01 AM
    Moderator
  • 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

    Friday, June 17, 2016 6:06 AM
  • 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.

    Friday, June 17, 2016 6:16 AM
    Moderator
  • 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.

    Wednesday, June 22, 2016 9:06 AM
    Moderator
  • 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

    Wednesday, June 22, 2016 12:54 PM
  • 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.

    Thursday, June 23, 2016 3:31 AM
    Moderator
  • You can do whatever you need to. Unfortunately, there may not even be a solution to this issue.

    Christopher

    Thursday, June 23, 2016 12:26 PM
  • 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.

    Friday, June 24, 2016 5:25 AM
    Moderator