Creating a query from an external comma-delimited list of words

[bobrace]

Here's my issue:

I have a table (let's call it "tableA) with about 7 fields. One of the fields contains information that I need to do a query on so that I may find specific payees, for example:

The field is called "Payee" and in that field for each record, the information could be "At&T", "A T and T" or "Abercrombie & Fi", but there is also other information in that field along with At&T, etc., so the field for a record may look like this:
A T AND T MOBILI A T AND T MOBILI 10 90034 Bill Pay
or
ABERCROMBIE & FI 3042 PP 10 9111 DIRECT

Is there a way to set up another table (let's call it tableB) to have all the Payees names (such as AT&T or whatever) in it and by the push of a button (maybe macro), have a query go out and find all matches from tableB in tablea?

So, basically have the query look in tableA and find all the matches from TableB. Please understand that tableA could have records that contain the same name, but spelled a number of different ways, for example, AT&T or A T and T or ATandT or ATT. Again these names may or may not be embedded in with other information in a field.

Or is there a way that an Access query can look into an external list and find the records, example:

Have a text (file) list with all the names (separated by a comma or space - AT&T, AT & T, Amercrombie and FI, Abercom&Fitch, etc. Now have a query go to that list and find all records that match in my tableA?

Thank you.

 

[John Big Booty]

Your data on the face of things would appear to be rather de-normalised

With a more normalised data structure this task should become a whole lot more straight forward.

 

[bobrace]

I believe this query would be pretty easy, but I just cannot seem to wrap my brain around how to accomplish it. Although, I am not new to DBs, I am to Access. Basically here's what I would like to do:
Take a list of words (that I have in an Excel Spreadsheet) which consists of a one, two or three word names in each row (about 1000 rows), but they all reside in a single column and use these to find matches in my query. So if I'm looking for a record that contains AT&T* in the DB's table "Payee" field, Access would look in this (Excel) column of 1000 words and if it finds a match in that field, pull it as a found record from my DB. I think this can be accomplished by setting an Access Table to do an external look up, but I just cannot figure out the proper way to do this. Any help would be much appreciated.
Thank you

 

[MSAccessRookie]

I believe this query would be pretty easy, but I just cannot seem to wrap my brain around how to accomplish it. Although, I am not new to DBs, I am to Access. Basically here's what I would like to do:
Take a list of words (that I have in an Excel Spreadsheet) which consists of a one, two or three word names in each row (about 1000 rows), but they all reside in a single column and use these to find matches in my query. So if I'm looking for a record that contains AT&T* in the DB's table "Payee" field, Access would look in this (Excel) column of 1000 words and if it finds a match in that field, pull it as a found record from my DB. I think this can be accomplished by setting an Access Table to do an external look up, but I just cannot figure out the proper way to do this. Any help would be much appreciated.
Thank you

Actually, as John pointed out, the process should be very easy, but the lack of organization of your data makes it much more difficult than it has to be.

In the past, when I have been forced to import denormalized data, I inserted a step into the import process that took known exceptions and converted them to the proper entries for the database. As an example, you would create a Table of Vendors to be used by your programs and a Table of Exceptions to be used only during the import of denormalized data (see below for examples)

All references to vendors would use the VendorID Field from the Vendor Table, and additional informatioin is obtained by linking Tables. When an exception is encountered during Import, the value from the Exception Table is to be used if there is one.

This approach will provide a goos start, but what to do if there is no value found also needs to be determined.

tblVendors
    VendorID
    VendorName
    ... Additional Vendor Related Information ...
 
tblException
    ExceptionID
    ExceptionName
    VendorID