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

Wed Oct 21 13:17:41 CDT 2020

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_campaign=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_campaign=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_campaign=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_campaign=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_campaign=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_campaign=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
>