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