Share via

Removing duplicates in Access

Anonymous
2016-02-02T15:30:45+00:00

Hello,

I have a question about removing duplicates in Access.  In Excel, there is a function/task on the Data tab that says Remove Duplicates, and you can select columns (fields in Access) that you want it to use for identifying those dups.  Then, it actually removes all but one row that contain that individual info.  In Access, I know there is a query where you can identify duplicates, but I want to do in Access what you can do in Excel, and actually remove all but one record with a particular result in that field.  Does anyone know how I can accomplish this?  I'm working with a set of data that has too many rows to use Excel to accomplish this. 

Any help on this would be appreciated.

Thanks!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-02-24T19:30:34+00:00

    Phyl04,

    I was just trying to solve this problem as well. After a bunch of unsuccessful searching, I found a solution that is not intuitive at all but very simple to apply.

    Here's the description of it:

    "There is a bit of a clumsy way to get that. First you right click and Copy the table but when you choose Paste you have the option of Structure Only. Choose that. Then set whatever field is the unique ID (SSN, Trans #, EE badge # etc) to Primary Key. Paste the first table again, this time choose Append to Another table. You will get an error message that due to the Primary Key a certain amount of rows will not be appended thereby removing the dupes."

    Source (in the comments): 

    https://www.youtube.com/watch?v=JEy0uUIYeX4&list=PL4UezTfGBADBmCOYtQ8QohflQNY1y3oE7&index=14

    For a visual demonstration of this process, check out this video. Unfortunately, there isn't any audio in it, but by using the description from above and this visual I was able to do exactly what I needed to do. Hopefully you can too.

    https://www.youtube.com/watch?v=Sl\_ujaTjxEs

    Best of luck,

    -Michael

    60+ people found this answer helpful.
    0 comments
  2. Anonymous
    2016-02-02T18:05:30+00:00

    Here's a simple example which would delete all rows bar one with the same LastName value, retaining the row with the highest (MAX) value of the key (ContactID) per last name:

    DELETE *

    FROM Contacts AS C1

    WHERE ContactID <>

         (SELECT MAX(ContactID)

          FROM Contacts AS C2

          WHERE C2.LastName = C1.LastName);

    If the table does not currently have a candidate key, simply add an autonumber column to act as the key.

    20+ people found this answer helpful.
    0 comments
  3. Anonymous
    2016-02-02T15:52:49+00:00

    Here is an example for removing duplicates. plug in the fields that constitute a dup. Be sure to make a copy of the table first in case something goes horribly wrong. [grin]

    DELETE *  

    FROM T_Data  

    WHERE (SELECT  Count(*) 

           FROM T_Data AS T  

           WHERE Nz(T.F1, "") =  Nz(T_Data.F1, "") 

           AND Nz(T.F2, "") =  Nz(T_Data.F2, "") 

           AND Nz(T.F3, "") =  Nz(T_Data.F3, "") 

           AND Nz(T.F4, "") =  Nz(T_Data.F4, "")  

           AND T.ID <= T_Data.ID) > 1;

    9 people found this answer helpful.
    0 comments