Multi-Function Module/Macro/VBA

[andysgirl8800]

Peter,
Once again, thanks for your help. Yes, I'm certain my test records contain all the necessary data (all three fields are filled in) and I have created 2 pairs of duplicates, for sake of trial. I have a total of 10 records, 4 are duplicates, ideally, the query should leave me with 6 records. The existing duplicates query, build with the standard Duplicate query wizard in access, does nothing more than locate and display the dupicate records. I was hoping to use it and modify it to not only FIND them, but to REMOVE them completely from the table.

 

[Bat17]

OK try this in a query then, but make sure you test it on a back up first!!

DELETE tblHalfTab2.MemberNum
FROM tblHalfTab2
WHERE (((tblHalfTab2.MemberNum) In (SELECT [MemberNum] FROM [tblHalfTab2] As Tmp GROUP BY [MemberNum],[MBRDrug],[MBRLast] HAVING Count(*)>1  And [MBRDrug] = [tblHalfTab2].[MBRDrug] And [MBRLast] = [tblHalfTab2].[MBRLast])));

Peter

 

[andysgirl8800]

Thanks Peter,
At what point should I insert this bit into my existing code? Should it be it's own query, separate from my duplicates query?

 

[andysgirl8800]

Peter...
YOU'RE A GENIUS!! Thanks SO much! I put that code into it's own query and ran it (on a back up as you suggested) and it worked like a charm!!! Kudos to you!

 

[Bat17]

Glad we got it sorted

Peter