How to Query Partial Match from Two Tables

[Pat Rowe]

Relative Newbie w/ a stumper! Any help is appreciated. Thanks!

I have two tables that I have imported to access. I'd like to query between these two tables for records that have a shared last name (under a name field) in both tables.

It's a bit complicated because the name fields in the two tables are formatted differently, and sometimes inconsistently. For example: "ROGER D & BETTY JO COLE" in table 1, becomes "COLE ROGER D" in table 2.

I've seen lots of good information about joining the tables, but that seems more difficult here (I'd need to split up the name field data --difficult with middle initials and two part names [eg., Mary Jo] and then find a way to join the appropriate pieces).

Is there a better way to search for matches (or, possible matches), such as using a wild card search (eg., Like *COLE*) that would work on a whole sale basis?

THANKS!

[Denburt]

Personally in this situation I would think that you could use a union query then you could search both tables for the information and start working on a cleanup solution.

This would have to be done in SQL view of a query, of course and would look something like:

Select [last name], "Table1" as Table1 From [Table one]
Union Select [last name], "Table2" as Table2 From [Table Two]

[Pat Rowe]

Thanks for the suggestion, Denburt. I'm not very familiar with SQL, so I'll have to do some googling before I know how well that will work --- again, one complication is that the name fields aren't neatly separated into first, middle, and last name fields.

I may be totally off-base here, but any idea why this doesn't work:

SELECT Table1.*, Table2.*, Table1.FullNameTb1
FROM Table1, Table2
WHERE (((Table1.FullNameTb1) Like "*[Table2].[FullNameTb2]*"));


Thanks again.
P.

[Denburt]

@Pat Rowe
Hmm that's an interesting approach. You can probably use that although you would need a slight adjustment.

SELECT Table1.*, Table2.*, Table1.FullNameTb1
FROM Table1, Table2
WHERE (((Table1.FullNameTb1) Like "*" & [Table2].[FullNameTb2] & "*"));

The way you posted it you were looking for the text [Table2].[FullNameTb2] in the actual field and not the data in [Table2].[FullNameTb2].

[Bytten]

Thanks - that's a perfect solution to a look up I was trying to do. I just got rid of the need for about 50 individual 'like' queries