Yes as Vinu said we need a relationship between Term and Attend.

I have tried the below query and it is not exactly matching your requirement but it might help you...

Select A.ID, S.TestName,T.Term,SUM(A.PeriodNBR)/8

from dbo.TestScores S

join dbo.Attend A

on A.ID = S.ID

join dbo.Terms T

on S.SchoolYR = T.SchoolYR

and S.Term = T.Term

and S.LocID = T.LocID

left join dbo.Terms T1

on S.SchoolYR = T1.SchoolYR

and S.Term = T1.Term+1

and S.LocID = T1.LocID

Where A.DateABS between case T.term when 1 then '2012-06-01' else T1.EndDate End and T.EndDate

group by A.ID,T.Term,S.TestName