two tables, different field formats, need to search

[bairdgbaird]

How can I search for LASTNAME and FIRSTNAME fields from TABLE_1 contained in NAME in TABLE_2?

TABLE_1 contains 80,000 records and has FIRSTNAME and LASTNAME fields.

TABLE_2 has 1 million records and just one field for NAME. Complicating matters, this NAME field is not uniformly formatted. NAME sometimes includes includes middle initials. It also sometimes contains two names, again, not always in a uniform fashion. So it could include the following entries:
1. Doe, John
2. Doe, John F.
3. John Doe
4. John F. Doe
5. Doe, John & Jill
6. Doe, John F. & Jill
7. Doe, Jill & John



I want to return matches where people from TABLE_1 appear in TABLE 2. I know I can do a series of left, right, mid, and instr functions to split NAME in TABLE_2, but with 1 million records that's a lot of work and am looking for a smarter approach.

Please help.

[weekend00]

Following query can match firstname and lastname in NAME, but it will also match (john, doe) with "Johnny Doe" or "xjohny sdoej" and so on.

select * from table_1,table_2 where [name] like "*" & lastname & "*" and [name] like "*" & firstname & "*"

[bairdgbaird]

This is great. Can I do this in the graphical query interface or do I need to do it as SQL or something? If the latter, could you tell me how? I've never done that. If it can be done with the drag and drop interface, then how would I show the relationship between the tables?

I really appreciate the help.

[weekend00]

You can do it in design view (graphical).

no relationship (no join) between the two table, all relationship is in WHERE clause.