johnmerlino
Registered User.
- Local time
- Today, 08:19
- Joined
- Oct 14, 2010
- Messages
- 81
Hey all,
I have a table called FinalForgotten which contains this:
aname (field)
Smith John
Smith,John
Smith,Jane
I have a table called defense_final which contains this:
last_name (field) first_name (field) middle_initial (field)
Smith John T
Smith,John Ruby
Smith,Jane Lis
Marie,Ann Sam
Simms,Bob
Kent,Joe
I am trying to build table that checks the first and last names of the two tables (even though FinalForgotten only has a single field by default which contains both the first and last names) and if there is a match, then return the first and last and middle initial into a new table:
FinalForgottenWithMiddle
Smith John T
Smith,John Ruby
Smith,Jane Lis
In order to do this, I have this query:
Problem is it gives a missing operator syntax error highlighting the "AS" clause. Not sure why and do you think there are other errors in this as well?
Thanks for response.
I have a table called FinalForgotten which contains this:
aname (field)
Smith John
Smith,John
Smith,Jane
I have a table called defense_final which contains this:
last_name (field) first_name (field) middle_initial (field)
Smith John T
Smith,John Ruby
Smith,Jane Lis
Marie,Ann Sam
Simms,Bob
Kent,Joe
I am trying to build table that checks the first and last names of the two tables (even though FinalForgotten only has a single field by default which contains both the first and last names) and if there is a match, then return the first and last and middle initial into a new table:
FinalForgottenWithMiddle
Smith John T
Smith,John Ruby
Smith,Jane Lis
In order to do this, I have this query:
Code:
SELECT left([aname],
IIF(instr([aname], ",") = 0, InStr(1,[aname]," ")-1),InStr(1,[aname],",")-1)
& ", " &
right([aname],
IIF(instr([aname], ",") = 0,Len(aname)-InStr(1,[aname]," "),Len(aname)-InStr(1,[aname],",")
& " " &
defense_final.middle_initial AS fullname INTO FinalForgottenWithMiddle
FROM FinalForgotten INNER JOIN defense_final ON
((right(FinalForgotten.aname,IIF(instr([aname], ",") = 0,Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname]," ")),Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],","))=defense_final.first_name))
AND
((left(FinalForgotten.aname,,IIF(instr([aname], ",") = 0,InStr(1,FinalForgotten.[aname]," ")-1)),InStr(1,FinalForgotten.[aname],",")-1))=defense_final.last_name);
Problem is it gives a missing operator syntax error highlighting the "AS" clause. Not sure why and do you think there are other errors in this as well?
Thanks for response.