Need to know how to delete duplicate records in Access

[smack]

I was able to get a query out showing all the duplicate records but know I need the duplicates to be sent to another table

any ideas?

[Andrew Thackray]

Use the INTO keyword after the select part of your query listing duplicates IE

Select Field1,Field2 etc INTO NewFile from ATable where etc.

This turns the query into a make table query & it will place its output into the file specified

[PEB]

I agree with Andrew but he don't say anything about deleting the dublicate rows in your table :) I bit annoying task to be done...

So I suppose that you have a query that retrieves all dublicates on a field named My_field... So in your query you have My_field and number of repetitions colum with values greater than 1.

In your table with the records to delete add a column of type Yes/No named to_delete

So I suppose that in your table you have also an ID that is unique without dublicates

So create second query that retrieves the first row from the dublicates with min ID... Use the top values

Create a thirth query to retrieve the dublicates values retrieve the top values from the previous query and tell that the values in this one should be different from the id of the top values query...

See the results in SELECT view if you are ok to place those rows on a new location than you should transform this query in Update query
and update to_delete column to Yes

Then follow the instructions of Andrew and delete them...

:)

[Andrew Thackray]

If the entire record is duplicated try this approach

1) Creater a query to dump the uniue set of records into a temporary table

 
Select  Distinctrow  * into TempTable from OriginalTable
 
 

now delete the original table and rename the temp table to the original table name
(After checking the query worked properly, of course)

[PEB]

Hey,

There is better way without creating complementary columns...

Once the query with dublicates created...

In a Delete query you can include the dublicate query results as condition:

IN (SELECT Dublicated_field FROM DUBLICATES)

ALSO invoke the query with the TOP Values UNDER THE ID COLUMN:

NOT IN (SELECT TOP 1 ID FROM MYTABLE WHERE Dublicated_field IN (SELECT Dublicated_field FROM DUBLICATES))

And you can execute your delete query based on your table with those conditions

:)