Table Join on Text

[stosh59]

I have two tables where the only unique item between the two may be first name and last name. I say "may be" because the tables are from two different sources, and I am trying to find where names from Table A match to names in Table B.



The key to both tables is random sequence assigned by Access.

Table A has 32,678 rows
Key (1,2,3,4,5....)
Last Name
First Name
And lots of other stuff.

Table B has 456 rows
Key (1,2,3,4,5...)
Last Name
First Name

I can't just join on Last Name for obvious reasons. I have tried all combination of joins with varying results; none of which are useful. This should be easy, shouldn't it?

Thank you for your guidance in advance!

[Tigger]

Connecting on text fields can always be tricky depending on the cleanliness of the source information. But if you are looking to connect on first and last name, use both of those fields as joins.

If you are building a query, they you can look to show all records from one table on only those matching from a second (left/right joins).

[stosh59]

Thank youTigger. So I tried a left join:
SELECT PrevEv.[Last Name], PrevEv.[First Name], CFList.[Last Name], CFList.[First Name]
FROM PrevEv LEFT JOIN CFList ON (PrevEv.[First Name] = CFList.[First Name]) AND (PrevEv.[Last Name] = CFList.[Last Name]);

And I got a list of all the names in the PrevEv list, but none from the CFList.

I have manually browsed both tables and I know there are matches. Any other ideas?

Thanks!

[Tigger]

stosh - if you are not getting any matches, then it may actually be data related. are there space characters in one source and not the other? You may need to do some scrubbing before trying to match.

[stosh59]

I think you are right. Thanks for the ideas!