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

[rocky smolin]

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>
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>
<#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>
> >
> 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: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>
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>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com