Results 1 to 5 of 5

Thread: Best Way to Deduplicate Records but leave one
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2011
    Location
    Raleigh, NC
    Posts
    682
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Best Way to Deduplicate Records but leave one

    I have an extremely large table (4.8MM records) that is the result of users importing excels with the same format into this table. From what I can tell, there are a significant number of duplicates within this population (likely from users importing the same excel twice). The tricky part is that I want to remove duplicates where all fields are the same but leave at least one record. In Excel, I would just concatenate all the fields into a new column and then deduplicate the table on that concatenated field. Is there a way to do this in access?

    Thanks,

    J
    60% of the time it works every time.

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,759
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Best Way to Deduplicate Records but leave one

    Write a SQL statement (a Query) that employs Select DistinctRow. Here is a quick link to a tutorial

    http://www.fmsinc.com/MicrosoftAcces...es_records.asp
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular
    Join Date
    May 2011
    Location
    Raleigh, NC
    Posts
    682
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Best Way to Deduplicate Records but leave one

    I took a look but I am not sure how that would remove the dupes?
    60% of the time it works every time.

  4. #4
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,759
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Best Way to Deduplicate Records but leave one

    It would create a query result that only lists unique rows. If you then need that somewhere else, you could do a MakeTable query, export to Excel, include in a report, etc. which only have the unique information.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    https://excel.solutions/2017/11/powe...te-code-video/


  5. #5
    Board Regular
    Join Date
    May 2011
    Location
    Raleigh, NC
    Posts
    682
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Best Way to Deduplicate Records but leave one

    Thanks. The problem is that even deduplicated it is over 2MM rows. I ended up building a new table and appending the uniques to it.
    60% of the time it works every time.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •