Compare equal data between two tables with different formats?

[ITguy1981]

I had trouble naming the title for this thread, but here is my issue. I have two tables. Both contain the fields "LASTNAME", "FIRSTNAME", and "MIDNAME". I need to compare table1 to table2 to if a person is in both tables. This is needed because a company has their table and they receive an update of names so thus they need to compare their data. I'm having an issue because the updated excel file that i'm importing for the comparison sometimes uses a full middle name where the companies data only contains one letter for the middle name. I currently have relationships between the three fields. I would like to be able to search equal between LASTNAME, FIRSTNAME, and 1st letter of MIDNAME. I don't know how to specify just the first character. So when working if one table has Mary A Roberts and the second has Mary Alice Roberts it will see them as being equal.

 

[Galaxiom]

The criteria for the query's join have to be set in SQL as the Query Designer does not support functions in the join.

Something like this:

SELECT whatever
FROM Table1 JOIN Table2
ON Table1.LASTNAME = Table2.LASTNAME
AND Table1.FIRSTNAME = Table2.FIRSTNAME
AND Table1.MIDNAME = Left(Table2.MIDNAME,1)

Just open the SQL view of your query and change it slightly.

 

[ITguy1981]

Thanks a bunch. I kept getting syntax errors, but then I realized there was a 1 after fieldname of MIDNAME to notate how many characters from the left.

 

[ITguy1981]

Let's mark this thread as solved.