Text Search Problem

[Jürgen Germonpré]

Hi,

I've posted this in two groups since i don't know if my problem should be
solved by code or can be done by a query.

Situation:
I have this publicity-thingie running, where people can gain points when
they eat a lot of cookies. Afterwards they sending in a coupon + a certain
amount of money and they get T-shirts or whatever sent to them. The coupon
is encoded in a database together with a bank-account number which will be
used to transfer the amount to our account. This is part one.

On the other hand I have a file (.CSV) which I receive from my bank
containing all transfer activity on my account. So all I have to do is match
both the "encoded" accountnumber from the coupon and the accountnumber in
the file and update the payments in the same or another table. No problem so
far.

However I still get lot's of unattributed records in the CSV because of
errorneous encodings. (unreadable writing on the coupon, another
accountnumber used or whatsoever) So the only way for me, to get a max
matchings is to search firstname, lastname and address fields. Now I have +-
2000 recs to match and I do it one by one by means of a simple Find
action... no need to explain this is pretty medieval...
I wondered if there's a way to automate this (f.i. via regular expressions,
queries,etc...) to get a listing with probable matches for each unmatched
payment, by searching through firstname, lastname and address fields. So I
could reduce lookup time a lot.

Any suggestion would be welcome !

Thanks.

JG

 

[John Nurick]

Hi Jürgen,

The first thing I'd try would be to join the two tables on various
groups of fields (or partial fields), e.g.

Surname
First letter of first name
Postcode

and look for matches that way.

Or if you want to take the opportunity for a fuzzy learning experience,
do a web search for "Levenshtein distance".

 

[Jürgen Germonpré]

Dear John,

This is definitely a useful link. Although I'm by far something like a math
genius i'll try to figure it out.
Thanks a lot.

 

[Robert Morley]

This being an Access group, you're probably strictly using Access, but if
you happen to be using SQL Server as a back end, it has a couple of "fuzzy
logic" functions, like SoundEx() that you could use on the names.



Rob

 

[Douglas J. Steele]

I wrote about both SoundEx and Levenshtein Distance in my April, 2005
"Access Answers" column in Pinnacle Publication's "Smart Access"

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Robert Morley]

Yes, but Doug, what HAVEN'T you written about?


Rob