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

rocky smolin rockysmolin2 at gmail.com
Wed Oct 21 11:46:15 CDT 2020


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



More information about the AccessD mailing list