I am having trouble identifying dup

[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;

 

[benjamenus]

What is the purpose of this? Is it to delete the duplicate instances? If so, you could select the first instance of all records which have duplicates and delete those. Keep running this until there are no records with duplicate instances.

Access intricacies are not my strong point, but the following may help...

This will delete your duplicate rows (in Oracle - as Oracle uses ROWID) - not sure if Access uses something similar?

delete from TABLE
where rowid in (
select min(rowid)
from TABLE t2
having count(UNIQUE_FIELD)>1
group by UNIQUE_FIELD
);

 

[cvaccess]

I really need help with Access though it nice to know it works with Oracle. I read some historical threads that creating a new table with a primary key on the field that I want to filter out the duplicates and then running an append query. I tried this and still get all records that have duplicates. My goal is to have it leave one copy of each duplicate. How specifically can I do this with Access?

BTW, the reason I need this is for data integrity.

Please help anyone.

Thanks.

 

[benjamenus]

Sounds like a challenge to me! I've done a bit of experimenting and come up with a couple of solutions - neither of which is ideal, but both work!

SOLUTION 1
+ Design a 'make table' query that selects all the records from your table (that contains the duplicates)
+ Select the Properties for that query
+ Change the 'Unique Values' property to 'Yes'
+ Run the query
+ You have a new table with no duplicates

SOLUTION 2
+ Replicate your Database (accept the 'make backup' option)
+ This introduces a unique field called s_GUID
+ Run the following query

DELETE Table1.*
FROM Table1
WHERE Table1.s_GUID in
(SELECT MIN(s_GUID)
FROM Table1 T1
GROUP BY ID
HAVING count(ID)>1);

+ Keep runnning it until it deletes no more records
+ You are left with only the records you wish

*** CAUTION ***
Please read up on replication before you proceed with SOLUTION 2
support.microsoft.com/support/ kb/articles/q282/9/77.ASP
***************

Like I said, I'm sure there is a neater way to do this, but this (esp. SOLUTION 1) should do the trick for now. Please let me know if this was of any use.

P.S. You could use some VBA to achieve the same thing.

 

[benjamenus]

Well, I did think there should be an easier way to do this, but if Microsoft can't think of one (see

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209183)

then who am I to argue?

 

[benjamenus]

One more solution you may wish to look at, though it all seems like rather too much effort:

http://www.fabalou.com/access/queries/delete_dupe_records.htm

And now the final word ... I promise!

You mention that this is for data integrity - which is great - but going forward I'd strongly recommend changing the design of the database so that it will not allow the addition of duplicate records.