dup problem

[cvaccess]

I am having trouble identifying duplicates. When I run the query below. I get duplicates including the original record. How could I modify this to get just the true dups? For example, record 1 has duplicates record 2 and 3. I only want record 2 and 3 to show in this query. Please help.
Thanks.
SELECT DISTINCTROW pend_list.TP, pend_list.CLAIM_NO, pend_list.MEMBER_NO, pend_list.PROVIDER, pend_list.PROV_TYPE, pend_list.RECD_DATE, pend_list.ENTER_DATE, pend_list.PEND_ERROR, pend_list.RPT_DATE, pend_list.SCRUB_DATE, pend_list.PROC_CODE
FROM pend_list
WHERE (((pend_list.TP) In (SELECT [TP] FROM [pend_list] As Tmp GROUP BY [TP],[CLAIM_NO],[MEMBER_NO],[PROVIDER],[PROV_TYPE],[RECD_DATE],[ENTER_DATE],[PEND_ERROR],[RPT_DATE],[SCRUB_DATE] HAVING Count(*)>1 And [CLAIM_NO] = [pend_list].[CLAIM_NO] And [MEMBER_NO] = [pend_list].[MEMBER_NO] And [PROVIDER] = [pend_list].[PROVIDER] And [PROV_TYPE] = [pend_list].[PROV_TYPE] And [RECD_DATE] = [pend_list].[RECD_DATE] And [ENTER_DATE] = [pend_list].[ENTER_DATE] And [PEND_ERROR] = [pend_list].[PEND_ERROR] And [RPT_DATE] = [pend_list].[RPT_DATE] And [SCRUB_DATE] = [pend_list].[SCRUB_DATE])))
ORDER BY pend_list.TP, pend_list.CLAIM_NO, pend_list.MEMBER_NO, pend_list.PROVIDER, pend_list.PROV_TYPE, pend_list.RECD_DATE, pend_list.ENTER_DATE, pend_list.PEND_ERROR, pend_list.RPT_DATE, pend_list.SCRUB_DATE;

 

[cvaccess]

Can anyone help with this?

Basically, I need to find a way to leave one copy of each duplicate. Currently, I get all records for duplicates. So when I run a delete query it does not delete all duplicate records...it leaves one copy of each duplicate (the original record).

Please help anyone.

Thank you.

 

[Pat Hartman]

There is no way with a query to bring back all but 1 of the duplicates.

The easiest way to do this is to create a copy of your table with the correct key fields defined to prevent duplicates. You can create a multi-column primary key (up to 10 columns) by using the standard windows method for selecting multiple items (CNTL-Click or SHIFT-Click for adjacent entries). Once all the key fields are highlighted, press the key icon to create the primary key.

Once the new table is created, make an append query that selects all the rows from the original table and appends them to the new table. Click OK after the query runs to bypass the error message saying that some rows could not be added due to key violations. Once you are certain that all the data that you need has been transferred, you can delete the original table and rename the new table. The primary key will prevent future duplicates from being entered.

The above suggestion assumes that you don't care which of the duplicates gets saved.