Identifying Similar Field Values

[RogerCooper]

I need to use a query to find when products with similar ID's are in the same inventory location. Has someone designed a clever function that measures similarity?

My idea is to add up the ASCII values of each character in the ID field and consider them similar if the totals are close.

 

[MajP]

Yes, it depends what you mean. Regular expressions can identify patterns if you are looking for a specific pattern, and simil functions can find how similar a string is.

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

can you provide some data to show examples?

 

[MajP]

The examples are in the last post with FuzzyFind. Soundex, Levenshtein, and Simil algorithms.

 

[RogerCooper]

For example, I could easily identify that an AB-11 & an AB-12 are similar by adding the ASCII values. But that wouldn't work for an AB-11 & an AB-11CS.

 

[MajP]

Those algorithms I provided are far more advanced then that. I would take a look at that demo. I think for this no need to look at the soundex since these are not going to sound like anything. That works well to find Kathy and Cathie.
Look at the simil and Levenshtein. Should be able to mimic what I did.
You then would have a query or form to return the top N similar for verification.

 

[MajP]

So you have to try these functions against your data, because it really depends what your data looks like to determine which is best.
But they are fast and work pretty well. This data set has 10000 names and the results for all three come back instantly.
So I am looking for Aaron Kertzmann but have no idea how to spell it. So i spell it Ahron Kerzmen. All four listboxes include the top choices for that algorithm sorted by similiarity. You can see that all found the correct answer as the top choice. In this case the Soundex works since my bad spelling still sounds the same.