SELECT staff.StaffNo, semester.SemesterID, teach.SubjectID, teach.Sub2, teach.Sub3, teach.Sub4, teach.Sub5, teach.Sub6, teach.TeachLoad,
(IIf(IsNull( teach.SubjectID),0,1) + IIf(IsNull(teach.Sub2),0,1) + IIf(IsNull(teach.Sub3),0,1) + IIf(IsNull(teach.Sub4),0,1) + IIf(IsNull(teach.Sub5),0,1) + IIf(IsNull(teach.Sub6),0,1)) * 3 As teach.TeachLoad,
FROM (subject INNER JOIN (staff INNER JOIN teach ON staff.StaffNo = teach.StaffNo) ON subject.SubjectID = teach.SubjectID) INNER JOIN semester ON staff.StaffNo = semester.Staff

i try make query that will put the value of teach load automatically when i choose the subjects.

let say i key in two subject, so the value of the teachload will be 6 (1 sub = 3 teachload)

the problem is,i have error in my query. can u help me .

tq in advance :)

im using ms access 2007.

TeachLoad is a calculated field, so delete the first "teach.TeachLoad," and delete "teach." after the AS. also I suspect your final field reference in the join should be Staff.StaffNo=Semester.StaffNo.