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

Wed Oct 21 14:48:10 CDT 2020

He specifies that a duplicate record is one in which every field is
identical to another record. The record in this table has about 10 FKs so
deleting a record doesn't produce any orphans.

You're right that Stopping duplicate entries should be done before the
update, but that won't work for him.

r
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
James Button via AccessD
Sent: Wednesday, October 21, 2020 12:42 PM
To: 'Access Developers discussion and problem solving'
Cc: James Button
Subject: Re: [AccessD] Finding Duplicate Records when there are more than 10
fields

Firstly, with the results being just the entries that are repeated (not just
duplicated)  shouldn't the script be which to keep rather than which to
delete.
Then again if there are any other data columns in the rows with repeated
keys,
then shouldn't the cleanup process also facilitate a merge, or selection of
the
required other data

And - more 'the thing to do'  stop the problem occurring - Stop duplicate
entries being created in the first place 

Also consider the effect on any associated tables and other processes of
deleting a (Parent?) PK entry - depending on what the PK is actually
generated
by/from

But that's just me being picky in a VV&Y/UAT mode

JimB

-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of rocky
smolin
Sent: Wednesday, October 21, 2020 6:58 PM
To: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Finding Duplicate Records when there are more than 10
fields

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

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