Linking a key that is a subset of key on next table

[Lois Ann]

Our registration program collects "first" & "last" name in separate fields.
Our master list has names formatted as "Last;First;;;nickname". I'd like to
merge the input from our registration program as "Last;First" and link to the
key in the master list if it is included as a subset. e.g. The master
"Jones;Rebecca;;;Becky" would be linked if the registration program has
"Jones;Rebecca" or "Jones;Becky". I also need help with joining the first &
last name in the registration program. Thank you in advance.

 

[Sylvain Lafontaine]

You need something like (untested):

Select ...
From MasterList M inner join Registration R on (M.FullName Like (R.LastName
& ";*") And (M.FullName Like ("*;" & R.FirstName & ";*") Or M.FullName Like
("*;" & R.NickName))

If this doesn't work in Access, move the Join condition to the WHERE
statement:

Select ...
From MasterList M, Registration R
Where (M.FullName Like (R.LastName & ";*") And (M.FullName Like ("*;" &
R.FirstName & ";*") Or M.FullName Like ("*;" & R.NickName))

Of course, your best solution would be to redesign the master list in order
to separate the field "Last;First;;;nickname" into three or more separate
fields.

 

[Lois Ann]

Thank you Sylvain. I really appreciate your assistance! Have a nice
Thanksgiving! Lois