I have a crosstab query which produces the names of students (row) vs their exam scores whilst they've been at school (value) for each exam slot (column). Currently every student appears once which is fine as it fits into a form perfectly.
The only thing is some of the results are from a "foundation paper" and some are from a "Higher" paper and I'd like to differentiate between the two using conditional formatting.
Every time I add the extra field [test_tier] to the crosstab I get more rows than I want with many students appearing twice where they've sat exams in two tiers.
As a "cheat" I wondered if I could store the results as H45 or F45 depending on which level of exam they took but now I cant make the 45 appear in the textbox.
Running out of patience.
Hope someone can help
Code:
TRANSFORM Sum(IIf([tblcalendar].[papers]=1,[tblpaperstudent].[testscore]*3,[TblPaperStudent].[TestScore])) AS ts
SELECT qryActiveStudents.Firstname, qryActiveStudents.surname
FROM tblTestPaper INNER JOIN ((TblPaperStudent INNER JOIN tblCalendar ON TblPaperStudent.SlotID_FK = tblCalendar.SlotID_PK) INNER JOIN qryActiveStudents ON TblPaperStudent.studentID_FK = qryActiveStudents.StudentID_PK) ON tblTestPaper.PaperID_PK = TblPaperStudent.PaperID_FK
WHERE (((qryActiveStudents.MathsClass) = '7a/Mm1'))
GROUP BY qryActiveStudents.MathsClass, qryActiveStudents.Firstname, qryActiveStudents.surname, tblCalendar.Papers
ORDER BY qryActiveStudents.surname, qryActiveStudents.Firstname
PIVOT TblPaperStudent.SlotID_FK;
Here's the crosstab