Update table from other (multiple) tables. Please help!

[Max]

I am trying to write sql query for Access that would update one table
with information from the other two.
I know that I have to use INNER JOIN to connect the tables since FROM
doesn't work in Access Update queries. Here is what I have.

"UPDATE tblStudentsSummer " & _
"INNER JOIN STUDENTFAMILY ON " & _
"STUDENTFAMILY.ExtStudentID = tblStudentsSummer.StudentID " & _
"INNER JOIN tblStudents ON " & _
"tblStudentsSummer.StudentID = tblStudents.StudentID " & _
"SET " & _
"tblStudentsSummer.FirstName = tblStudents.FirstName, " & _
"tblStudentsSummer.LastName = tblStudents.LastName " & _
"WHERE STUDENTFAMILY.FunctionAction = 'UPDATE'"

I get the following exception when running this query: Syntax error
(missing operator) in query expression 'STUDENTFAMILY.ExtStudentID =
tblStudentsSummer.StudentID INNER JOIN tblStudents ON
tblStudentsSummer.StudentID = tblStudents.StudentID'.)

I know that this error is caused by second INNER JOIN statement
because I was running simular queries fine with just one INNER JOIN.
Can somebody please tell me what I am missing? Is something wrong with
this syntax?
Thanks in advance!

 

[Michel Walsh]

Hi,


With Jet? Try

"UPDATE ( tblStudentsSummer " & _
"INNER JOIN STUDENTFAMILY ON " & _
"STUDENTFAMILY.ExtStudentID = tblStudentsSummer.StudentID ) " & _
"INNER JOIN tblStudents ON " & _
"tblStudentsSummer.StudentID = tblStudents.StudentID " & _
...


A DISTINCTROW can also be required.

Hoping it may help,
Vanderghast, Access MVP