[AccessD] Duplicates Query

I usually

SELECT MAX(PKID) FROM (SELECT PKID, SomeField FROM SomeTable WHERE
COUNT(SomeField) >1)

If it looks good, then I delete them:

DELETE SomeTable FROM WHERE PKID IN (
SELECT MAX(PKID) FROM (SELECT PKID, SomeField FROM SomeTable WHERE
COUNT(SomeField) >1)
)

I think in Access you have to use TOP 1 instead of MAX

This will only take care of one at a time, so you will have to run it
again if you have triplicates (or more).



On Tue, Aug 31, 2010 at 9:42 AM, Rocky Smolin <rockysmolin at bchacc.com> wrote:
> Dear List:
>
> I used the find duplicates query wizard to make a query to find duplicate
> records in a table.  Is there a quick way to then delete all but one
> occurrence of the duplicated records?
>
> MTIA
>
>
>
> Rocky Smolin
>
> Beach Access Software
>
> 858-259-4334
>
> www.e-z-mrp.com <http://www.e-z-mrp.com/>
>
> www.bchacc.com <http://www.bchacc.com/>
>
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>