JohnMerlino JohnMerlino - 12 days ago 11
SQL Question

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

Answer

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.

Comments