split table, compare its splitted fields with another table, and then append content (1 Viewer)

Hey all,

I have this query, but it's giving me syntax errors:

SELECT s.fullname INTO SummaryJudgment_FinalForgottenWithMiddle
FROM (
SELECT(
(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
) AS s
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

Thanks for response

Just curious as to why you want to store the names like this
Leventhal,Raymond P

For almost all applications you can concatenate the fields as necessary when necessary.
Suggest you may want to consider using your
first_name last_name middle_initial as you format of choice.

In any case you could do it with a couple of queries,

1) create a temp table with separate name parts

2) SELECT [aname] & " " & [summary_judgment].[middle_initial] AS Newaname, summary_judgment.middle_initial INTO SummaryJudgmentWithInitial
FROM Summary_separateTemp INNER JOIN summary_judgment ON (Summary_separateTemp.Fname = summary_judgment.first_name) AND (Summary_separateTemp.LName = summary_judgment.last_name);