none
Trying to automate removal of a duplicate row in Access 2013 database RRS feed

  • Question

  • I am trying to automate a process in Access 2013 where I can delete a duplicate row in a database. What i need to do is delete one row for each of the duplicated employee lines. What I think I need to do is:
    SELECT TOP 1 [ID]
    FROM database AS DB
    WHERE (EmpID is grouped by like values; need syntax here)

    EmpID    Special#    Hours    Name       ID
    1001               2        FT        Batman    499
    1001               2        FT        Batman    495
    1025               2        FT        Robin       233
    1025               2        FT        Robin       228

    What I want is one of the two IDs for each of the employees so I can use the ID to delete the row. I do not care which of the two rows I delete. I should never have a situation where there is more than two rows that are the same.

    Thank you,

    Christopher

    Thursday, August 17, 2017 7:31 PM

All replies

  • Hi Christopher,

    One possible way could be something like:

    DELETE FROM TABLENAME

    WHERE ID IN(SELECT MAX(ID)

    FROM TABLENAME

    GROUP BY EMPID

    HAVING COUNT(*)>1)

    (untested)

    Hope it helps...

    Thursday, August 17, 2017 7:42 PM
  • That got me much closer so thank you for that. I'm still having one issue that I didn't explain in the above description. Every employee can have multiple lines but I need to delete only the duplicate lines where the employee has a 2 twice under Special#. Most every employee has multiple rows, but only one row with a 2 in that column. I need to delete one of the two lines that has a 2 in the Special# column if that makes sense.
    Thursday, August 17, 2017 8:35 PM
  • Hmm, what happens if you add a WHERE condition about having 2 in Special#? For example:

    DELETE FROM TABLENAME
    WHERE ID IN(SELECT MAX(ID)
    FROM TABLENAME
    WHERE [SPECIAL#]=2
    GROUP BY EMPID
    HAVING COUNT(*)>)

    Hope it helps...

    Thursday, August 17, 2017 8:41 PM
  • Thank you very much! I will give this a try. I won't be back at that client until Monday, but I'll let you know how it goes then. Have a great weekend!
    Friday, August 18, 2017 12:48 PM
  • Good luck! Have a great weekend!
    Friday, August 18, 2017 2:45 PM
  • That worked! Thank you for helping me. I don't think I would have ever figured it out searching Google.
    Tuesday, August 22, 2017 12:32 PM
  • Hi,

    You're welcome. Glad to hear you got it to work. Good luck with your project.

    Tuesday, August 22, 2017 2:39 PM
  • That worked! Thank you for helping me. I don't think I would have ever figured it out searching Google.

    Hello,

    I suggest you mark the helpful post as answer to close this thread. Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 23, 2017 3:14 AM
    Moderator