De-duplication

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

John

Hi

I have a table with a single column that has duplicate values. How can get
out of it a table with no duplicates in that column?

Thanks

Regards
 
I have a table with a single column that has duplicate values. How can get
out of it a table with no duplicates in that column?
Click to expand...

Create a new query on that table, and make it a Totals query with a
group by on that field. Then change its Query Type to MakeTable and
specify the new table name. When you run the query, you'll get a new
table with just the unique values.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Create a query that removes the duplicates, and use that query as the basis
for a MakeTable query:

INSERT INTO MyNewTable (Field1)
SELECT DISTINCT Field1 FROM MyOldTable
 
Hi

I have a table with a single column that has duplicate values. How can get
out of it a table with no duplicates in that column?

Thanks

Regards
Click to expand...

The simplest way would be to copy and paste the table to a new table, *design
mode only*, to create a new empty table. Select this field and put a unique
Index on it (field properties in the lower left of the table design window).
Make any other desired design changes to the table at this time.

Run an Append query to migrate the data from the current table into the new
one. You'll get a warning message "x records were not added due to key
violations" - that's the dups rattling into the bit bucket.

If there are other fields in the table, it's arbitrary and unpredictable which
of the duplicates will be kept and which discarded. If that's a problem post
back with more details.
 
John W. Vinson said:
The simplest way would be to copy and paste the table to a new table, *design
mode only*, to create a new empty table. Select this field and put a unique
Index on it (field properties in the lower left of the table design window).
Make any other desired design changes to the table at this time.

Run an Append query to migrate the data from the current table into the new
one. You'll get a warning message "x records were not added due to key
violations" - that's the dups rattling into the bit bucket.

If there are other fields in the table, it's arbitrary and unpredictable which
of the duplicates will be kept and which discarded. If that's a problem post
back with more details.
Click to expand...



The part I find fascinating about this topic is, Microsoft has provided us
with a "find duplicates query" it will gather all dupes for you. Had they
just given us a delete "all" dupes button we wouldn't be having this
conversation... just a thought.

Richard

PS
I think it is available on excel 2007 not sure about access
 
Back
Top