Member Avatar for huhubebey

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.

  • 2 Contributors
  • 1 Reply
  • 119 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by ChrisPadgham
Member Avatar for ChrisPadgham

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.