Pierre 12/7/2021 8:54:30 AM
I was curious to see if any answers would come to your question / I have struggled on the same issue.
Here's what I do - it is heavy, inelegant but partly adresses your question. I mainly write it down to 'participate'.
Basically, (I did not know about many-to-many relations), I recreate my own many-to-may relation and there I can have all the selection formulas I want.
So I have a COMPANY table. I want a unique [Name] identifier.
- I create a [CleanedName]= Upper Trim / Replace ( Space / Hyphens .)
- I create then pointers with 5 / 6 / 7 letters: [@CN5] = Left([CleanedName],5), [@CN6], ...
I create a COMPANYCHECK table with a [@CHECK] field
I create several relations from COMPANY to COMPANYCHECK using the [@CN5], [@CN6], fields as pointers
In COMPANYCHECK I count the number of children with 5/6/7 letters. I also concatenate the names of the children into a summary field.
In COMPANY I retrieve from COMPANYCHECK the counts and the concatenation
In each COMPANY I then have the information about potential duplicates and the name of the duplicates.