[AccessD] Eliminating duplicate rows

Hi Bruce:

You could use something like this:

select distinctrow first([Myfield1]) AS [Myfield1 Field],
                   first([Myfield2]) AS [Myfield2 Field],
                   count([Myfield1]) AS NumberOfDuplicates
              from MyTable
          group by Myfield1, Myfield2
having count([Myfield1]) > 1
   and count([Myfield2]) > 1

This will at least expose your duplicates. Note the use of Access's First
function to set the table position to the first match.

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Bruce Bruen
Sent: Thursday, June 12, 2003 6:41 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Eliminating duplicate rows


Dear List,

Due to a (ahem) programming feature, I now have a table with several
thousand rows of which hundreds are redundant.  I am loking for a way to
delete the redundant rows.

I have used the previously discussed method of using a max (recID)
subquery in a delete query before, but it will only eliminate one of the
duplicates at a time.

Short of running the delete query over and over until they are removed,
is there a way to get rid of all but one of the duplicates?

tia
Bruce

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com