Best Way to Deduplicate Records but leave one

[jscranton]

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

 

[alansidman]

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

http://www.fmsinc.com/MicrosoftAccess/query/distinct_vs_distinctrow/unique_values_records.asp

 

[jscranton]

I took a look but I am not sure how that would remove the dupes?

 

[alansidman]

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.

 

[jscranton]

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.