str search function help needed

[kchernak]

I have a list B of customer names (sloppy, typo's, variations, etc), and I need to identify potential matching names from a master list A. for every record in list B, I want to seach through all records in list A and have the resulting field vale for the function be a list of reference numberf of the potential matches. Something like:

B_name_column: Acme Tire
Function_column: Acne Tire ref123, Hacney Tire ref375, Smith Tire ref978, Acme Tire ref363.

The result should be a query with the same number of records as the B list, but the function result column would be a string with all the potential matches.

Any clue if this can be done with a for next loop or am I trying to figure out how to drive a nail with a screwdriver?

Thanks!

 

[SBendBuckeye]

How many records are you dealing with and do the above records match because some have tire and some have acme or are you looking only for embedded acme tire?

Good LucK!

 

[kchernak]

Well, I have a master list (list A) of perhaps 20k records with names and account numbers. One department does work relating to some of these records and provides the results in a separate file (the List B). For various political and attitudinal reasons, the simple act of providing the account number with the "B" list so that they can be accurately matched back to the "A" list from where they originated is beyond the willingness and capability of the pea brained folks in that department. So, rather than going through the laborious and time consuming process of manually hunting through the mainframe to find clues to identify the "A" record, I need to try and match names (potentially spelled any number of ways) from the "B" list (perhaps a few hundred records) to exact (easy) and "potential" (not so easy) matches from the "A" list. Note: records in the "A" list have 12 fields - none of which provide much unique identification assistance other than the name field. The mainframe files have tons of fields, which are for the most part not accessible for extract. The manual verification process would be reduced by the results of this qry I'm trying to build so the mainframe verifications would be minimized and specifically targeted.

hope this makes sense?
any suggestions - even if just directional, would be great.
Thanks!