[AccessD] Deleting Duplicates

Coincidentally I had a client call yesterday with this very problem.  He
send an mdb with the following message:

"Here is the file to be scrubbed of extraneous data.

The query shows the data so that the problem can be seen. If you go to
Invoice # 2294869, Product # 000472895, Line Number 11 you will see two
records, one with a fldInvoiceDetailPrice of $2.64 and a nearly duplicate
record with a price of $.0016. I would like to create a cleaned file without
the numerically smaller fldInvoiceDetailPrice of the two records. There are
roughly 4,000+ of these duplicates. Thanks in advance for your expertise! "

I tried to do it with queries, but in the end found it was easier to combine
a query with a bit of code. First, I added an autonumber field to the table
so that each record would have a unique identifier. 

The query was a summation query grouping by Invoice Number, Detail Line, and
Product number.  Then it had a count of Detail Line with a criterion of >1
so only the dups would be selected, and MIN on the unit price, so only the
lowest of the two would be selected.  At that point I couldn't figure out
how to make a delete query but this took only a couple of minutes to write
and deleted just the lower cost records as the client asked:

Dim db As DAO.Database
Dim rsDelete As DAO.Recordset
Dim rsQuery As DAO.Recordset

Set db = CurrentDb
Set rsQuery = db.OpenRecordset("qryFindTheDups1")
Set rsDelete = db.OpenRecordset("Select * FROM tblPPHPHSLawsonDataWork")

Do While rsQuery.EOF = False
    rsDelete.FindFirst "fldID = " & rsQuery!MinoffldID
    If rsDelete.NoMatch = True Then MsgBox "BARF!!!"
    rsDelete.Delete
    rsDelete.MoveFirst
    rsQuery.MoveNext
Loop

rsQuery.Close
Set rsQuery = Nothing
rsDelete.Close
Set rsDelete = Nothing
db.Close
Set db = Nothing

MsgBox "Done"


I charged him .75 hour for this and saved him hours of combing through the
42,000 records looking for the dups.

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Saturday, May 16, 2009 6:53 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Deleting Duplicates

Oh my! ;)

That makes sense though, I just didn't think it through. I had pancakes for
breakfast. :)

Susan H.



>
> It will delete the duplicates.  ALL of them!  And of course you 
> generally want to keep the FIRST record of a duplicate set.

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com