Like Function

[The_Doc_Man]

This problem is not soluble. (Stay with me, that was rhetorical.)

If your text box contains 292 72 0276 then do you want to match all records with 292? With 72? With 0276? You already said there is no pattern. The computer cannot be a mind reader. (Which is good because by now mine would have left home due to the stream of insults...)

So to solve an insoluble problem, you change the problem to one that can be solved and go forward from there. You MUST remember, in cases like this, that the computer is dumber than a box of rocks. It knows NOTHING with regards pattern recognition UNLESS you can program the pattern template into a search tool. So you have to decide to do one of several things:

1. Accept the need for manual input
2. Define some rules for matching
3. Restructure the input to have a pattern

That is an Either / Or list, not an And / Or list.

In the abstract, the computer has NO HOPE of doing what you want without guidance.

 

[Gismo]

Any chance you can export the 85K list into excel? I will demo here to see how easy to search with real data.

Hi

I have a list of a few items attached

 

[arnelgp]

i added another field MainSB and you need to update this field
using the supplied update query whenever you add new sb/ad no.

 

[MajP]

From what you describe, you really do not know how much "like is like" until you start the search. Basically you want some sub string or a super string, but you do not know how much of a sub string or if you want the super string.

Here is a refined version of the interface.
You pick from your list on the list. It will give you a list of all the full sub strings and partial substrings. Then you can choose how much "like" it has to be.

Example you pick 05-004. So if you select "05" you see that the returned list includes all things starting with 05*

But maybe you really meant things with "05-" which is a smaller list does not include things like "05A..." So search by "05-"
Or maybe you really wanted things like 05-00-4x. In that case you have to use a partial string.

 

[Gismo]

From what you describe, you really do not know how much "like is like" until you start the search. Basically you want some sub string or a super string, but you do not know how much of a sub string or if you want the super string.

Here is a refined version of the interface.
You pick from your list on the list. It will give you a list of all the full sub strings and partial substrings. Then you can choose how much "like" it has to be.

Example you pick 05-004. So if you select "05" you see that the returned list includes all things starting with 05*
View attachment 94365

But maybe you really meant things with "05-" which is a smaller list does not include things like "05A..." So search by "05-"
Or maybe you really wanted things like 05-00-4x. In that case you have to use a partial string.
View attachment 94366

Hi,

This really looks good but I see what you mean and I never actually looked at it this way as you mentioned how much like is like

I will filter it further by Category, when I look at Airframe I will not need to see any Engine documents
it will be difficult to filter by aircraft type as as some of the document refer to different type with very similar document numbers

Thank you so much for the assistance, I will most certainly implement this

 

[MajP]

Often with patterns you cannot fully automate something, but you can make an interface that makes it much easier to determine a potential pattern. It is easy for the human eye to see things are "similar" but maybe near impossible to come up with a rule set. One example is people looking for potential duplicates of a name. They may have data like
Mr. John Smith
Smith, Mr. John
J. Smith
Mr. Jon Smith
Mr. John Smith.
-Or some mispellings
Mr. John Smmith
Mr. John Smitth

Manually looking at this data a person could easily guess that these are potentially the same record. Writing code to do this would be near impossible without some AI.
But you can build a few tools to make finding these easy and clean them up as you find them.
Another common used tool is Soundex that can find things that sound alike. So
Jon Smith and John Smith will sound very similar
Kathy Jones and Cathy Joans will also be very similar

Matching logic

Probably not difficult but I would advise against it. In case where matches aren't exact, I would want someone to check the results rather than trust any algorithm I have a system for checking a batch of online payments by parents against the reference number for their child so it can be...

www.access-programmers.co.uk

I added a simpler version that may be a better UI for your case. I simply present the substrings from most complete backwards. This seems more logical. You would want to start at the top most restrictive down to least restrictive. No need for the multi select list box since you would never "and" or "or" these choices. The other examples still remain.