Re: Finding duplicates that might differ slightly

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Access is quite literal ... "duplicate" means an exact match, character for
character. Unless you can tell Access exactly how to decide what is/isn't a
duplicate, you may have to rely on USB (using someone's brain).

You and I (?!) can look at a list of CompanyNames and easily spot those that
are (probable) duplicates. For example, would you consider the following to
be duplicates?:

IBM
International Business Machines
IBM, Inc.
International Business Machines Corporation

If you or I weren't quite sure, we'd probably look at one/more of the
additional fields to help confirm/reject them as duplicates. Unless
instructed how, Access can't do that.

If you have 10 million records, you probably won't want to try this. If you
have 10,000 records or less, you might want to use a query to sort the
field(s) in alpha sequence, then eyeball the list.

Or, you could try to explicitly define how to evaluate each possible
"acceptable" difference that nonetheless still results in "duplicates".

By the way, if all four of those business names I listed are the same
business, which one is the "correct" name? Why does that matter? Because
any related tables/records will have to also be converted over to use the
record ID of the correct name!

Regards

Jeff Boyce
<Office/Access MVP>

"KBuser" <sakred@xxxxxxxxxxxxxxxxx> wrote in message
news:1136401296.365244.167230@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> I'm trying to create a query to find duplicate records, but some
> entries might contain a . or a '. How can I account for this in my
> query?
>
> SELECT BrandName.BrandName, BrandName.Suffix, BrandName.ProductClass,
> BrandName.Company, BrandName.updateDate
> FROM BrandName
> WHERE (((BrandName.BrandName) In (SELECT [BrandName] FROM [BrandName]
> As Tmp GROUP BY [BrandName],[Suffix] HAVING Count(*)>1 And [Suffix] =
> [BrandName].[Suffix])))
> ORDER BY BrandName.BrandName, BrandName.Suffix;
>
>
> Thanks in advance.
>


.

Relevant Pages

  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... I'm calling a function that will set the occurence number. ... If there's a duplicate, ... I used the query wizard to create a "find duplicates" query, now I need to adjust the results of that query to add an occurence counter/number, and I'd be set. ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ...
    (comp.databases.ms-access)
  • Re: Identifying Duplicate Records
    ... Indeed, the duplicate records ... Discovering the Find Duplicate query, ... or bring up the dupe records to look at them. ... have a find duplicate records wizard. ...
    (microsoft.public.access.formscoding)
  • Re: Identifying Duplicate Records
    ... the duplicate records problem represented one of the few occasions ... the Find Duplicate query, however, has allowed me to plug another gap in my ... or bring up the dupe records to look at them. ...
    (microsoft.public.access.formscoding)
  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... After playing around with duplicates, and a funtion to generate an occurence number, it looked like you would need a handful of queries per table!! ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ... On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? ...
    (comp.databases.ms-access)
  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... If there's a duplicate, ... I used the query wizard to create a "find duplicates" query, now I need to adjust the results of that query to add an occurence counter/number, and I'd be set. ... I have many many queries that check for orpaned data (for example, if there's a billing record with no matching custID in the customer table, I'm putting up a form with the unmatched data, and a quick way to update all unmatched to one archive type customer, etc. ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ...
    (comp.databases.ms-access)