Join 2 tables based on part of a field


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
 
Ad

Advertisements

K

KARL DEWEY

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

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

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
 
I

Ivan Grozney

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
 
Ad

Advertisements

J

Jeff Boyce

?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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top