Join 2 tables based on part of a field

[Ivan Grozney]

I have two tables that I need to join. I have no control over one of the
tables (table 2).
In table 1 I have LastName and FirstName. ex:
Bosco Bobby
Smith Randy

In table 2 I have First and Last name as well. ex:
Jones Jim Randy Fred Carol Angela Tina Eric
Smith Sarah Bill Barb Randy Tamara Tim

Table 1 has about 200 rows. Table 2 has 100,000 plus rows.

If I make a join
FROM tbl1 INNER JOIN tbl2 ON
tbl1.lastname = tbl2.lastname and
[tbl1].[frrstname] like '*' & [tbl2].[firstname] & '*'

I only get where the last and first names are exact matches. I need to
return the row with
Smith Sarah Bill Barb Randy Tamara Tim
since it matches
Smith Randy

Any ideas?

tia
Vanya

 

[KARL DEWEY]

Reverse the like --
FROM tbl1 INNER JOIN tbl2 ON
tbl1.lastname = tbl2.lastname and
[tbl2].[firstname] Like [tbl1].[frrstname] & '*'

 

[Jeff Boyce]

?!Ivan the Great?!

Jeff B.

 

[Ivan Grozney]

Karl,

Thank you so very much. I have been hammering at this for long time
and I guess my brain broke. Didn't notice that I had them backwards!

As Homer Simpon would say... D'OH!!!!!!

Vanya

Reverse the like --
FROM tbl1 INNER JOIN tbl2 ON
tbl1.lastname = tbl2.lastname and
[tbl2].[firstname] Like [tbl1].[frrstname] & '*'

--
Build a little, test a little.

I have two tables that I need to join. I have no control over one of the
tables (table 2).
In table 1 I have LastName and FirstName. ex:
Bosco Bobby
Smith Randy

In table 2 I have First and Last name as well. ex:
Jones Jim Randy Fred Carol Angela Tina Eric
Smith Sarah Bill Barb Randy Tamara Tim

Table 1 has about 200 rows. Table 2 has 100,000 plus rows.

If I make a join
FROM tbl1 INNER JOIN tbl2 ON
tbl1.lastname = tbl2.lastname and
[tbl1].[frrstname] like '*' & [tbl2].[firstname] & '*'

I only get where the last and first names are exact matches. I need to
return the row with
Smith Sarah Bill Barb Randy Tamara Tim
since it matches
Smith Randy

Any ideas?

tia
Vanya

 

[Ivan Grozney]

Jeff,

Ivan the Terrible.

?!Ivan the Great?!

Jeff B.

I have two tables that I need to join. I have no control over one of the
tables (table 2).
In table 1 I have LastName and FirstName. ex:
Bosco Bobby
Smith Randy

In table 2 I have First and Last name as well. ex:
Jones Jim Randy Fred Carol Angela Tina Eric
Smith Sarah Bill Barb Randy Tamara Tim

Table 1 has about 200 rows. Table 2 has 100,000 plus rows.

If I make a join
FROM tbl1 INNER JOIN tbl2 ON
tbl1.lastname = tbl2.lastname and
[tbl1].[frrstname] like '*' & [tbl2].[firstname] & '*'

I only get where the last and first names are exact matches. I need to
return the row with
Smith Sarah Bill Barb Randy Tamara Tim
since it matches
Smith Randy

Any ideas?

tia
Vanya

 

[Jeff Boyce]

?any relation to Vlad the Impaler?

<g> Jeff

Jeff,

Ivan the Terrible.

?!Ivan the Great?!

Jeff B.

I have two tables that I need to join. I have no control over one of
the
tables (table 2).
In table 1 I have LastName and FirstName. ex:
Bosco Bobby
Smith Randy

In table 2 I have First and Last name as well. ex:
Jones Jim Randy Fred Carol Angela Tina Eric
Smith Sarah Bill Barb Randy Tamara Tim

Table 1 has about 200 rows. Table 2 has 100,000 plus rows.

If I make a join
FROM tbl1 INNER JOIN tbl2 ON
tbl1.lastname = tbl2.lastname and
[tbl1].[frrstname] like '*' & [tbl2].[firstname] & '*'

I only get where the last and first names are exact matches. I need to
return the row with
Smith Sarah Bill Barb Randy Tamara Tim
since it matches
Smith Randy

Any ideas?

tia
Vanya