Delete Duplicates

[QB]

I have a table with 4 cols, but 2 of interest in this case

tbl name: dtMan
Cols: IngId, dtMan

I have currently have duplicate dtMan entries for various IngId and need to
delete the extra duplicate (Only keeping one entry)

Example (Existing Data)
ID Date
1 08-10-2009
1 08-10-2009
1 08-10-2009
1 08-03-2009
1 08-03-2009
1 06-25-2009
4 07-17-2009
4 07-17-2009
167 03-01-2008

(Wanted Result)
ID Date
1 08-10-2009
1 08-03-2009
1 06-25-2009
4 07-17-2009
167 03-01-2008

I was looking at the duplicate query, but how do I get it to delete the
extra entries while retain the original entry?

Thank you for the help,

QB

 

[Jerry Whittle]

If there aren't relationships defined in the Relationships window using that
table, you can do the following:

Create a Totals query and Group By both fields in question.

Use something like First, Last, Min, or Max for the other two columns.

Run and check the results.

If satifified, change the query to a Make Table and run it using a slightly
different name for the new table such as dtManNew.

Rename table dtMan to dtManBackup.

Rename the new dtManNew table to dtMan.

 

[vanderghast]

Either copy the data into a table which won't accept duplicated values
(through an index): you will get an error message telling you that some
records are not copied, that is OK, that is exactly what you want.

Either make a TOTAL query (GROUP BY) and once that works to display only not
dup data, make a new table out of it, or append the total-data to a new
table. Would help to add an index which won't allow duplicated value on this
new table.

Since there is no primary key, in your actual table, that makes a partial
delete difficult to realize since SQL works only on VALUES in the fields,
NOT ON POSITION of the record, and so, nothing really differenciate two
records having the same value, in each and every of these two records.
Having a criteria to delete one will also apply to delete the other record!
With a primary key, it would be possible to identify each record. In fact,
you can use a recordset (which will add a bookmark to each record) and try
to delete each record having the same values that the previous (undeleted)
record, but that is probably more trouble than using one of the two
procedures given here up.



Vanderghast, Access MVP