De-duplicating data

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

I have a column of values in a table. Some are duplicate. How can I remove
the duplicates such that no value is duplicate in the column?

Thanks

Regards
 
Hi

I have a column of values in a table. Some are duplicate. How can I remove
the duplicates such that no value is duplicate in the column?

Thanks

Regards
Click to expand...

Do you want to permanently delete the records where the one field is
duplicated? or see a list of the values with duplicates suppressed? or edit
the records in the table to replace the duplicated values with some other
values? More details please! A few rows of current sample data and desired
outcome might help.
 
Hi John,

Create a grouped query. Then use this query as a source of data in a make
table query (or an append query, if the new table is already present). For
example, in the Northwind sample database (Northwind.mdb), we can create a
unique list of cities for the customers in the Customers table. The steps
listed below are for Access 97 through 2003:

1.) Create a new query. Select Design View. Add the Customers table.
2.) Add the City field to the QBE (Query By Example) grid.
3.) Set an ascending sort, if desired.
4.) Click on the greek sigma button (Totals) in the toolbar. You should see
a new row appear in the QBE grid, "Total:", with "Group By" as the default.
5.) Run the query by clicking on the maroon exclamation button (or click on
Query | Run from the menu). Inspect the results. You should see a listing of
69 unique cities.
6.) Still in query design view, click on Query | Make-Table Query... using
the menu. Give the new table a reasonable name. Run the query once again.
7.) Open your new table in design view. Set a unique index on the City field.
Note: You may want to set this field as a primary key (which is unique), or
you may want to add a new Autonumber field, set as primary key, with the City
field indexed uniquely.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Copy the de-duped list into a second table if possible.
Click to expand...

That's really easy then. Create a new Table (you can copy and paste the
existing table to a new one, *structure only*, not copying the data). Put a
unique Index on the combination of fields which define a duplicate. Run an
Append query to copy the data into the new table; you'll get a warning message
that "x records were not copied due to key violations" - these are the
duplicates you didn't want.
 
Back
Top