JOIN question - unlike fields

[joave]

Hi:

I need to join two tables together for a query. The first table is an
index table where each name only appears once in that table. The problem is
that the name field in the second table has extra characters in it (which I
also need to use). So here is the example - Field from first table: "Smith,
Joe". Field from second table: "Smith, Joe (Location 1)"

What is the best way to find what dates (the date is another field in the
second table) Joe Smith appears? Please keep in mind that raw data from the
second table is from an online database and the field is not editable prior
to importing it into Access - there are 20000 records right now and it would
be difficult to separate out the names from the extra data in that field
afaik.

Thank you for your help!

Dave

 

[John W. Vinson]

Hi:

I need to join two tables together for a query. The first table is an
index table where each name only appears once in that table. The problem is
that the name field in the second table has extra characters in it (which I
also need to use). So here is the example - Field from first table: "Smith,
Joe". Field from second table: "Smith, Joe (Location 1)"

What is the best way to find what dates (the date is another field in the
second table) Joe Smith appears? Please keep in mind that raw data from the
second table is from an online database and the field is not editable prior
to importing it into Access - there are 20000 records right now and it would
be difficult to separate out the names from the extra data in that field
afaik.

Thank you for your help!

Dave

Well, joining on names is A Very Bad Idea since you very well might have two
or three people who happen to all be named Joe Smith... but:

you can use a LIKE operator in a JOIN clause:

SELECT <whatever>
FROM Table1 INNER JOIN Table2
ON Table2.[namefield] LIKE Table1.[Namefield] & "*"


will find all the matches on the name (whether they are the same or a
different person).