How to SQL JOIN on concatenated columns in MS Access? -

i have 2 tables column x , y concatenated represent unique identifier. want find rows in tableb not exist in tablea , add them tablec.

-------tablea--------   // tablea master refernce table names far |__x__|__y__|_name__| |  3  |  7  | mary  | |  3  |  2  | jaime |  -------tableb--------   // tableb input file daily names (some repeats exist in tablea) |__x__|__y__|_name__| |  2  |  5  | smith | |  3  |  7  | mary  |  -------tablec--------   // tablec temporary holding table new names |__x__|__y__|_name__| |     |     |       |  desired result:  -------tablec--------   // tableb - tablea = tablec |__x__|__y__|_name__| |  2  |  5  | smith | 

i want match rows based on concatenated x+y value. sql query far looks this:

insert tablec  select * tablea left join tableb    on tablea.x & table.b = tableb.x & tableb.y tableb.x & tableb.y null 

however, not give me intended result. cannot use exists actual data set big. give me suggestions?

i don't think slowness caused exists. query slow because you're trying use concatenation match multiple columns. use and instead , make sure have composite index on (x,y):

this select unique rows in tableb don't have same (x,y) value in tablea. note rows same x,y different name show in result (i.e. 2,5,joe appear). if don't want that, have group x,y , decide name want in case of duplicate x,y different name.

select distinct x,y,name  tableb b not exists (   select 1 tablea   a.x = b.x   , a.y = b.y )