Match data between 2 tables

[mcquam]

I am using CR11 and MS Access.

I have 2 separate user name tables; 1 has full name and the other has short name. Every user has both. I want to find records that have different user names between the two fields to find input errors. Obviously a straight comparison does not work.

Sample data:

[b]Short name   User name[/b]
TinaS         Tina Smith
SharonB       Sharon Brown

How can I select only non-corresponding records?

 

[lbass]

left({table.shortname}, len({table.shortname})-1) <> split({table.username}," ")[1]) or
right({table.shortname},1) <> split({table.username}," ")[2][1]

This assumes that there is a link between the two tables on a field like {table.id}.

-LB