Comparing Lists of similar but not Exact text

[Guest]

I have two tables that have similar data

Example:

Table 1
Smith, John

Table 2
Smith John

These are the same except for the comma. How do I write a query that when
this type of situation occurs, it would see these two values as equal and
return a match.

I am using a Join expression

 

[Jeff Boyce]

Brad

Access isn't very smart, so you'll need to tell Access exactly how to do the
comparison.

One approach would be to use InStr() to "find" commas, then Left(), Mid()
and Right() functions to strip out the comma, preserving a space. At this
point, you should have comparable values (comparing "no commas against no
commas") -- unless there are other subtle differences, like suffixes and
middle names?!

 

[Guest]

Jeff, Thanks.

Could you provide me a sample statement based on the field list in my first
question? I have looked at what you said and it makes sense, however, I am
not sure what to type and where to place it. Thanks