Please bare with me this is difficult to explain xD
http://img90.imageshack.us/i/croppercapture1.png/
This is based on an undergraduate degree course where a student takes many units (say 4 core units and 1 optional unit per year). tblAwardCoreUnits and tblAwardOptUnits store which units are optional and core for which award, hence the relationship to tblAward and tblStudentCoreUnits and tblStudentOptUnits store the particular instances of those units which a particular student is taking.
Secondly, a unit can have multiple events (say a lecture and a unit) and each of those events has sessions in which a student can attend, hence tblEvents, tblSessions and tblAttendances.
The query I am trying to produce is to get a list of all level one students, grouped by their award that lists the percentage of attendances in all the units in the current level.
I've tried and tried with this and the following is the best I've managed to come up with so far... I'd REALLY appreciate any help you can give with this!
SELECT tblStudents.enrolmentNo, tblStudents.forename, tblStudents.surname, tblAwards.title, (SELECT COUNT((tblAttendances.attended + tblAttendances.authorisedAbsence)) AS SumOfAttendances FROM tblAttendances INNER JOIN (tblStudents ON tblStudents.enrolmentNo = tblAttendances.enrolmentNo)) "divided by the sum of total possible attedances in tblSessions where a student takes a unit multiplied by 100"
FROM tblUnits, tblAwards
INNER JOIN ((tblStudents INNER JOIN tblStudentOptUnit ON tblStudents.studentID = tblStudentOptUnit.studentID) INNER JOIN tblStudentCoreUnit ON tblStudents.studentID = tblStudentCoreUnit.studentID) ON tblAwards.awardID = tblStudents.awardID
WHERE (((tblStudents.level)="1") AND ((tblStudents.status)="enrolled"))
GROUP BY tblAwards.title
ORDER BY tblStudents.forname;