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

[Stuart McLachlan]

SELECT Count(Key) AS CountOfKey, First(Key) AS FirstOfKey, Last(Key) AS 
LastOfKey,f2,f3,f4,f5....
FROM tblMyTable
GROUP BY f2,f3,f4,f5...;
HAVING Count(Key)>1;

On 21 Oct 2020 at 9:46, rocky smolin 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&u
> tm_cam paign=sig-email&utm_content=webmail> Virus-free. www.avg.com
> <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&u
> tm_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
>