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

Above error message occurs with this statement:

 SELECT f.fullname INTO SummaryJudgment_FinalForgottenWithMiddle
FROM (
(SELECT Left([aname],InStr(1,[aname],",")-1)) As lastname FROM
SummaryJudgment_FinalForgotten) & " " & (SELECT
RIGHT([aname],InStr(1,[aname],",")+1)) As firstname FROM
SummaryJudgment_FinalForgotten) & " " & (SELECT
summary_judgment.middle_initial AS middlename FROM summary_judgment)
) AS fullname
FROM SummaryJudgment_FinalForgotten AS f INNER JOIN summary_judgment
AS s ON f.lastname = s.last_name && f.firstname = s.first_name;

Basically this is what two tables look like (note they will have more fields than 1 where last or first name of different fields can be si开发者_开发百科milar):

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 to create a new table that is like SummaryJudgment_FinalForgotten but with the middle initial from summary_judgment appended: Leventhal,Raymond P

You do not need to write 3 select statements to concatenate the values into one field.

select left(...) & right(...) & initial AS fullname INTO SummaryJudgment_FinalForgottenWithMiddle
 FROM SummaryJudgment_FinalForgotten

Are you trying to use two ampersands to represent a logical AND?

FROM SummaryJudgment_FinalForgotten AS f INNER JOIN summary_judgment
AS s ON f.lastname = s.last_name && f.firstname = s.first_name;

I don't think that's legal for Access' Jet/ACE database engines. Try it with the AND keyword in place of &&.

OTOH, I wonder if you can do something simpler.

SELECT last_name & "," & first_name & " " & middle_initial AS fullname
INTO SummaryJudgment_FinalForgottenWithMiddle
FROM summary_judgment;

This works:

SELECT left([aname],InStr(1,[aname],",")-1) & " " 
     & right([aname],Len(aname)-InStr(1,[aname],",")) & " "
     & summary_judgment.middle_initial AS fullname 
INTO SummaryJudgment_FinalForgottenWithMiddle
FROM SummaryJudgment_FinalForgotten, summary_judgment;

Though you might want this instead:

SELECT left([aname],InStr(1,[aname],",")-1) & ", " 
     & right([aname],Len(aname)-InStr(1,[aname],",")) & " "
     & summary_judgment.middle_initial AS fullname 
INTO SummaryJudgment_FinalForgottenWithMiddle
FROM SummaryJudgment_FinalForgotten, summary_judgment;

The second version gives you the comma after the last name. Note that Right counts from the right, which is why you have to subtract the InStr value from the length.

EDIT:

The code I gave above works with your sample data--one row in each table. With more rows, it gives a cross product of (LastName, FirstName) x MiddleInitial. It occurred to me that that might be the case, so I went back to my test & added a second row--it is true. So then I tried to write the join expression....

Access doesn't like this:

... ON left([aname],InStr(1,[aname],",")-1) = last_name ...

It throws the error "Join expression not supported." Changing it to this:

... ON (trim((left(SummaryJudgment_FinalForgotten.aname,InStr(1,[aname],",")-1))=trim(summary_judgment.last_name))) ...

results in a query that runs & creates the table, but doesn't create any rows (the same was true before I added the "trim" calls in an attempt to fix it).

So I tried specifying the table for all occurences of aname. No joy--until I realized that I was making the wrong comparison (derived-last to last and derived-first to last--oops).

Using the following FROM clause with either above SELECT ... INTO 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) AND 
    ((right(SummaryJudgment_FinalForgotten.aname,Len(SummaryJudgment_FinalForgotten.aname)-InStr(1,SummaryJudgment_FinalForgotten.[aname],","))=summary_judgment.first_name));

It might even work correctly without the full qualification of each field now that I'm joining first to first & last to last (since there is no duplication across the tables), but having proven that it does work, I'm done.