Can update on Union query

[stormin_norm]

I can't update a union query, so what to do? build form/subforms, write some vba code to perform inserts/updates? I need help query pro's!

I have a table Student:
studentID
LN
FN
cohort

I have a table Grade:
studentID
course
grade

five students in the cohort. I am building a form which lists ALL five students, even though four may not have taken the course. But I still have to list all five, since I may enter a grade for this course in the next month. Then, I will only have five student records, and two grade records.

My issue is: If I do this with a union, I can view all the names and any grades already posted, but cannot update or insert into a union query. What technique would work best?

select student.studentID, student.LN, student.FN, student.cohort, grade.course, grade.grade
from student inner join grade on (student.studentID=grade.studentID)
Where ((student.cohort="122") AND (grade.course="CS101"))
UNION
select student.studentID, student.LN, student.FN, student.cohort, null as course, null as grade
from student
Where ((student.cohort="122"))
AND (student.studentID NOT IN (select student.studentID from student inner join grade on (student.studentID=grade.studentID)
Where ((student.cohort="122") AND (grade.course="CS101"))));
/* last select required to ensure no duplication */

 

[stormin_norm]

Thanks Pat.

I tried that first, and only retrieved 1 row. The one student who has a grade for cs101, the other students do not yet have grades, hence the union join.

I think the only solution is to insert into temp table the union query, allow the user to update the temp table. Then run an update/insert into the regular tables.