Close Matches???

[Bill]

Hi All,

I have two large tables (year 1 and year 2) of financial transactions that
contain a field with supplier name. I need to compare both tables for
entries against suppliers however the entry of the supplier name is not
consistent. The supplier name will be in there somewhere but the text string
will not necessarily be in a consistent format.

I maybe off the mark here but I think that a Google like comparison that
returns some sort of indicator of the closeness of the comparison would
help.

Is anyone aware of any queries or code that might help me here please?

Regards.
Bill.

 

[Tom van Stiphout]

One of your options is Simil, an algorithm that compares two strings
and returns a value between 1 (identical) and 0 (completely
different). It can be used for ranking. I recently wrote a chapter
about Simil for this book:
SQL Server MVP Deep Dives
www.manning.com/nielsen/
Well worth the purchase, and all proceeds to to War Child.
The download package for that chapter includes a classic DLL with the
Simil algorithm. You can call that from VBA.

-Tom.
Microsoft Access MVP

 

[Dorian]

There is nothing built in to Access that will do that. You will have to write
your artificial intelligence routine.
You should not identify suppliers by name in your main table. Suppliers
should be in a separate suppliers table with an autonumber primary key. Only
the foreign key should be in your main table. Because this was not done, you
are faced with this problem.
You need to figure out in what ways the same supplier is described
differently and base your routine on that.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".