I have one queston... are your students ever in more than one cycle? if they are you don't really have the table structure to support that. You are storing the cycle of each student on your students table which means they will only ever have *1* cycle at a time (in other words you won't be able to report history of data for any student)
As long as that's your intent this query should give you what you want:
Code:
SELECT Students.[Last Name], Students.[First Name], [Cycle Table].Cycle, [Cycle Table].[Start Date], [Cycle Table].Midpoint, Sum(IIf([attendance date] Between [start date] And [midpoint],[hours],0)) AS HoursatMidpoint, Sum([Student Attendance].[hours]) AS TotalHours
FROM [Cycle Table] RIGHT JOIN (Students LEFT JOIN [Student Attendance] ON Students.ID = [Student Attendance].Student) ON [Cycle Table].ID = Students.Cycle
GROUP BY Students.[Last Name], Students.[First Name], [Cycle Table].Cycle, [Cycle Table].[Start Date], [Cycle Table].Midpoint;