Joining on a substring of a column

In standard SQL, this is accomplished using string functions:

select * 
  from EEqry E
inner
  join LoanOfficers L 
    on case when position(' ' in E.Firstname) = 0
          then E.FirstName
          else substring(E.FirstName from 1 
                for position(' ' in E.Firstname)-1)
        end           
     = case when position(' ' in L.Firstname) = 0
          then L.FirstName
          else substring(L.FirstName from 1 
                for position(' ' in L.Firstname)-1)
        end

Notice how a CASE expression is used to ensure that if the name does not actually contain a space, then the comparison will use the whole name.

In Access syntax, this becomes:

select * 
  from EEqry E
inner
  join LoanOfficers L 
    on iif(instr(E.Firstname,' ') = 0
            , E.FirstName
            , left(E.FirstName
                 , instr(E.Firstname,' ')-1)
          )           
     = iif(instr(L.Firstname,' ') = 0
            , L.FirstName
            , left(L.FirstName
                 , instr(L.Firstname,' ')-1)
          )

Some people dislike Microsoft Access because its version of SQL is [quote]so[unquote] non-standard, but honestly, it's not that bad once you get to know it!