Join 2 tables based on part of a field

  • Thread starter Thread starter Ivan Grozney
  • Start date Start date
I

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
 
Reverse the like --
FROM tbl1 INNER JOIN tbl2 ON
tbl1.lastname = tbl2.lastname and
[tbl2].[firstname] Like [tbl1].[frrstname] & '*'
 
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

KARL DEWEY said:
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.


Ivan Grozney said:
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
Click to expand...
Click to expand...
 
Jeff,

Ivan the Terrible.

Jeff Boyce said:
?!Ivan the Great?!

Jeff B.

Ivan Grozney said:
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
Click to expand...
Click to expand...
 
?any relation to Vlad the Impaler?

<g> Jeff
Ivan Grozney said:
Jeff,

Ivan the Terrible.

Jeff Boyce said:
?!Ivan the Great?!

Jeff B.

Ivan Grozney said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top