To make it simple - just leave the last duplicate.  In other words if I have
8 records that are dupes just delete the first 7
Ben

"Roy Lambert"  wrote in message
news:272617EF-A103-44B4-B734-DFA6F7FCE01E@news.elevatesoft.com...

Ben

>Now comes the finale. I need to delete all duplicate records EXCEPT for
>one - the first or the last.

I don't know if Aage's suggestion will work or not, but before I have a look
at it I need a question answered - what do you mean by first or last. Is it
a random choice - could be either at the program's choice or is the user
allowed to specify which is to be left, or should both the first and last
duplicates be left or something entirely different?

Roy Lambert

Ben



This

DELETE FROM Contacts
WHERE

(
COALESCE(_Surname,'')+COALESCE(_Forename,'') IN
(SELECT DISTINCT COALESCE(X._Surname,'')+COALESCE(X._Forename,'') FROM Contacts X GROUP BY COALESCE(X._Surname,'')+COALESCE(X._Forename,'') HAVING COUNT(*) > 1)
)

AND

(
_ID NOT IN (SELECT Max(_ID) FROM Contacts A
WHERE COALESCE(_Surname,'')+COALESCE(_Forename,'') IN (SELECT DISTINCT COALESCE(X._Surname,'')+COALESCE(X._Forename,'') FROM Contacts X GROUP BY COALESCE(X._Surname,'')+COALESCE(X._Forename,'') HAVING COUNT(*) > 1)
GROUP BY COALESCE(_Surname,'')+COALESCE(_Forename,''))
)

should do the trick.


It will not be mega fast, and comes with no guarantees. If it escapes and destroys your home town its nothing to do with me!


Roy Lambert

This works
Thanks a mil

"Roy Lambert"  wrote in message
news:2E733D22-E7FC-4C62-8D1C-0487B696F5CF@news.elevatesoft.com...

Ben



This

DELETE FROM Contacts
WHERE

(
COALESCE(_Surname,'')+COALESCE(_Forename,'') IN
(SELECT DISTINCT COALESCE(X._Surname,'')+COALESCE(X._Forename,'') FROM
Contacts X GROUP BY COALESCE(X._Surname,'')+COALESCE(X._Forename,'') HAVING
COUNT(*) > 1)
)

AND

(
_ID NOT IN (SELECT Max(_ID) FROM Contacts A
WHERE COALESCE(_Surname,'')+COALESCE(_Forename,'') IN (SELECT DISTINCT
COALESCE(X._Surname,'')+COALESCE(X._Forename,'') FROM Contacts X GROUP BY
COALESCE(X._Surname,'')+COALESCE(X._Forename,'') HAVING COUNT(*) > 1)
GROUP BY COALESCE(_Surname,'')+COALESCE(_Forename,''))
)

should do the trick.


It will not be mega fast, and comes with no guarantees. If it escapes and
destroys your home town its nothing to do with me!


Roy Lambert