[AccessD] Finding Duplicate Records when there are more than 10 fields

rocky smolin rockysmolin2 at gmail.com
Sat Oct 24 12:09:10 CDT 2020 

Dear List:

So here's the code that identifies the duplicate records - duplicate being
defined as EVERY field in the two subject records is identical  - in case
someone runs into the same requirement.

I did it in a nested loop so that each pair would be tested only once. It's
a bit brute force but it works fast enough for the user. The PK of the
duplicates ends up in a front end table and from there I will display them
to the user so that he can select which one(s) to delete. Haven't written
that part yet. 

Private Sub cmdDuplicates_Click()

Set db = CurrentDb
db.Execute "Delete * FROM tblDuplicateTaskIDs"
Set rsDuplicates = db.OpenRecordset("Select * FROM tblDuplicateTaskIDs")

Set rsDup1 = db.OpenRecordset("Select * FROM tblTasks ORDER BY fldTaskID")

Do While rsDup1.EOF = False
   
    Set rsDup2 = db.OpenRecordset("Select * FROM tblTasks WHERE fldTaskID >
" & rsDup1!fldTaskID & " ORDER BY fldTaskID")

    Do While rsDup2.EOF = False
    
        If fnDuplicate = True Then
            
            rsDuplicates.AddNew
            rsDuplicates!fldDuplicateTaskID = rsDup1!fldTaskID
            rsDuplicates.Update
            
            rsDuplicates.AddNew
            rsDuplicates!fldDuplicateTaskID = rsDup2!fldTaskID
            rsDuplicates.Update
        
        End If
        
NextDup2:
        rsDup2.MoveNext
    Loop

NextDup1:
    rsDup1.MoveNext
Loop

MsgBox "Done"

HTH

Rocky

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Paul Hartland via AccessD
Sent: Wednesday, October 21, 2020 11:18 AM
To: Access Developers discussion and problem solving
Cc: Paul Hartland
Subject: Re: [AccessD] Finding Duplicate Records when there are more than 10
fields

Rocky,

Yeah sounds ok unless someone comes up with something better.  Glad I could
help.

Paul

On Wed, 21 Oct 2020, 18:58 rocky smolin, <rockysmolin2 at gmail.com> wrote:

> Paul:
>
> The table at the moment has only 116 records and as time goes on it may
> have
> several hundred - less if the user deletes the old records - this is a
task
> list and the tasks that are in the past could probably be deleted.
>
> So maybe it would be just as efficient to create a recordset using that
> extended WHERE statement for each record (tblTask.fld1 = val of current
> tblTask.fld1...etc.), check if the recordset has more than one record,
and,
> if so add the PKs to a temp table (assuming they're not already there) and
> then present the results by joining the PK of the temp table to the PK of
> the Task table?
>
> Tks
>
> r
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Paul Hartland via AccessD
> Sent: Wednesday, October 21, 2020 10:30 AM
> To: Access Developers discussion and problem solving
> Cc: Paul Hartland
> Subject: Re: [AccessD] Finding Duplicate Records when there are more than
> 10
> fields
>
> Rocky,
> you could have something like
>
> select a.*
> from yourtable as a
> join (theduplicatequeryhere) as b
> on a.f1 = b.f1 and a.f2 = b.f2 etc for all the 30 fields
>
> Paul
>
> <
>
http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
> paign=sig-email&utm_content=webmail
>
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
paign=sig-email&utm_content=webmail>
> >
> Virus-free.
> www.avg.com
> <
>
http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
> paign=sig-email&utm_content=webmail
>
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
paign=sig-email&utm_content=webmail>
> >
> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
> On Wed, 21 Oct 2020 at 17:47, rocky smolin <rockysmolin2 at gmail.com> wrote:
>
> > Paul:
> >
> > I think that worked.  But it only identified one duplicate. I'll have to
> > ask
> > the client to send me the latest back end and give me examples of
> > duplicates
> > that he knows of.  Since he wants this change, I'm assuming he's seeing
> > more
> > than a couple duplicates.
> >
> > Next problem is to find the PKs these two (or more) so I can present
them
> > to
> > him in a form and he can select one to delete.
> >
> > Thanks and regards,
> >
> > Rocky
> >
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > Paul Hartland via AccessD
> > Sent: Wednesday, October 21, 2020 9:18 AM
> > To: Access Developers discussion and problem solving
> > Cc: Paul Hartland
> > Subject: Re: [AccessD] Finding Duplicate Records when there are more
than
> > 10
> > fields
> >
> > Rocky,
> >
> > Can you not just write a bit of vba code to connect to the database and
> > have some sql like below run and return the results to a recordset
> >
> > select f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14, f15,
> > f16, f17, f18, f19, f20, f21, f22, f23, f24, f25, f26, f27, f28, f29,
f30
> > from yourtable
> > group by f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14,
> f15,
> > f16, f17, f18, f19, f20, f21, f22, f23, f24, f25, f26, f27, f28, f29,
f30
> > having count(*)> 1
> >
> > Paul
> >
> > <
> >
>
http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
> > paign=sig-email&utm_content=webmail
> >
> <
>
http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
> paign=sig-email&utm_content=webmail
>
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
paign=sig-email&utm_content=webmail>
> >
> > >
> > Virus-free.
> > www.avg.com
> > <
> >
>
http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
> > paign=sig-email&utm_content=webmail
> >
> <
>
http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
> paign=sig-email&utm_content=webmail
>
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
paign=sig-email&utm_content=webmail>
> >
> > >
> > <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
> >
> > On Wed, 21 Oct 2020 at 17:01, rocky smolin <rockysmolin2 at gmail.com>
> wrote:
> >
> > > Dear List:
> > >
> > >
> > >
> > > I have a client running a db I made for him which has a table of 30
> > fields.
> > > He wants to identify duplicate records meaning all 30 fields
(excluding
> > the
> > > PK of course).  But the duplicate query wizard has a limit of 10
> fields.
> > > So
> > > I think this needs a piece of VBA code.
> > >
> > >
> > >
> > > Does anyone have a good procedure for doing this?
> > >
> > >
> > >
> > > MTIA,
> > >
> > >
> > >
> > >
> > >
> > > Rocky Smolin
> > >
> > > Beach Access Software
> > >
> > > 760-683-5777
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> > --
> > Paul Hartland
> > paul.hartland at googlemail.com
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> Paul Hartland
> paul.hartland at googlemail.com
>
> <
>
http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
> paign=sig-email&utm_content=webmail
>
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
paign=sig-email&utm_content=webmail>
> >
> Virus-free.
> www.avg.com
> <
>
http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
> paign=sig-email&utm_content=webmail
>
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
paign=sig-email&utm_content=webmail>
> >
> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

More information about the AccessD mailing list