Neil,
Please check
Eliminating duplicates in a table FAQ #8129
You can not use RECNO(), etc. functions on a select with multiple tables involved.
>Hi,
>
>I am trying to clean up duplicates in an existing database.
>
>The problem is that with the select statement below I am getting the record number of the cursor dups and not the record number of the file xpat.
>
>Any ideas.
>
>
>SELECT RECNO() as recno, xpat.patid, xpat.lastname, xpat.firstname ;
>FROM xpat ;
> JOIN ( ;
> SELECT xpat.patid, COUNT(*) AS nCount ;
> FROM xpat ;
> GROUP BY patid HAVING nCount > 1) Dups ;
> ON xpat.patid = Dups.patid ;
> ORDER BY xpat.patid, xpat.lastname ;
> INTO CURSOR HasDups
>
>Join Dups and delete all but lowest record number
>
If it's not broken, fix it until it is.
My Blog