sql - Syntax Error in Join Operation in MS-Access when splitting and comparing records -

above error message occurs statement:

 select f.fullname summaryjudgment_finalforgottenwithmiddle ( (select left([aname],instr(1,[aname],",")-1)) lastname summaryjudgment_finalforgotten) & " " & (select right([aname],instr(1,[aname],",")+1)) firstname summaryjudgment_finalforgotten) & " " & (select summary_judgment.middle_initial middlename summary_judgment) ) fullname summaryjudgment_finalforgotten f inner join summary_judgment s on f.lastname = s.last_name && f.firstname = s.first_name; 

basically 2 tables (note have more fields 1 last or first name of different fields can similar):

summaryjudgment_finalforgotten (table) aname (field) leventhal,raymond (data)   summary_judgment (table) first_name(field) raymond (data) last_name (field) leventhal (data) middle_initial (field) p (data) 

ultimately, i'm trying create new table summaryjudgment_finalforgotten middle initial summary_judgment appended: leventhal,raymond p

this works:

select left([aname],instr(1,[aname],",")-1) & " "       & right([aname],len(aname)-instr(1,[aname],",")) & " "      & summary_judgment.middle_initial fullname  summaryjudgment_finalforgottenwithmiddle summaryjudgment_finalforgotten, summary_judgment; 

though might want instead:

select left([aname],instr(1,[aname],",")-1) & ", "       & right([aname],len(aname)-instr(1,[aname],",")) & " "      & summary_judgment.middle_initial fullname  summaryjudgment_finalforgottenwithmiddle summaryjudgment_finalforgotten, summary_judgment; 

the second version gives comma after last name. note right counts from right, why have subtract instr value length.

edit:

the code gave above works sample data--one row in each table. more rows, gives cross product of (lastname, firstname) x middleinitial. occurred me that might case, went test & added second row--it is true. tried write join expression....

access doesn't this:

... on left([aname],instr(1,[aname],",")-1) = last_name ... 

it throws error "join expression not supported." changing this:

... on (trim((left(summaryjudgment_finalforgotten.aname,instr(1,[aname],",")-1))=trim(summary_judgment.last_name))) ... 

results in query runs & creates table, doesn't create rows (the same true before added "trim" calls in attempt fix it).

so tried specifying table occurences of aname. no joy--until realized making wrong comparison (derived-last last , derived-first last--oops).

using following clause either above select ... does work correctly:

from    summaryjudgment_finalforgotten inner join    summary_judgment on      ((left(summaryjudgment_finalforgotten.aname,instr(1,summaryjudgment_finalforgotten.[aname],",")-1))=summary_judgment.last_name) ,      ((right(summaryjudgment_finalforgotten.aname,len(summaryjudgment_finalforgotten.aname)-instr(1,summaryjudgment_finalforgotten.[aname],","))=summary_judgment.first_name)); 

it might work correctly without full qualification of each field i'm joining first first & last last (since there no duplication across tables), having proven work, i'm done.