>I use this code to find duplicate records in my tables. I did not invent it. I found it in an article in Foxpro amgazine years ago by Tamar Ganor.
>
>SELECT *, ;
> COUNT(*) AS dups ;
> FROM ach_final ;
> GROUP BY ach_final.orgtracnum ;
> HAVING COUNT(*) > 1 and not empty(orgtracnum) and nsf_fee#20.0000 ;
> INTO TABLE duplicate.DBF
>
>INDEX ON (orgtracnum + str(amount) + Reasoncode + alltrim(mid)) TAG bigmess2
>SET ORDER TO bigmess2
>SELECT ach_final
>SET RELATION TO (orgtracnum + str(amount) + Reasoncode + alltrim(mid)) INTO duplicate
>COPY TO ach_finaldups FOR FOUND('duplicate') && gives me the duplicate records
>
>
>Orgtracnum is really a key since they should never be duplicated in my data. The nsf_fee part is there to keep the dupe records for this charge out. This record mirrors the original except it has a $20 fee in this field.
>
>I keep getting records in here that should not come in the results. Am I doing something wrong?
>
>Thanks in advance for your help! :-)


The values you get for any field other than OrgTracNum in DUPLICATE will be random after the GROUP BY is performed. They will have the values for the last record of each distinct OrgTracNum.

If you want to filter out records with nsf_fee not equal 20 you should be putting that in the WHERE clause. The HAVING clause (I believe) does not operate until after the grouping has been peformed

SELECT OrgTracNum, ;
COUNT(*) AS dups ;
FROM ach_final ;
WHERE NOT EMPTY(orgtracnum) AND nsf_fee # 20.0000;
GROUP BY ach_final.orgtracnum ;
HAVING COUNT(*) > 1;
INTO TABLE duplicate.DBF

will give you all the duplicate OrgTracNums and the total number of records for each.


That having been said........
Assuming that what you want is all the records for duplicate OrgTracNum ..try

SELECT *, ;
FROM ach_final ;
WHERE NOT EMPTY(orgtracnum) AND nsf_fee # 20.0000;
AND orgtracnum IN (SELECT orgtracnum FROM ach_final GROUP BY orgtracnum ;
HAVING COUNT(*) > 1);
INTO TABLE duplicate.DBF


HTH..........Rich