Conditional formatting in a crosstab query

[andy49]

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

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

[isladogs]

If TestScore is a number field this won't work.
Either change the datatype or create a concatenated field in your underlying query "H" & TestScore or "F" & TestScore

OR use IIf statement to select the H tier if it exists otherwise use F tier

[andy49]

Thanks for the prompt reply.

I've managed to store the information in an extra field but cannot get this data to display on the form as anything else but "H45" which isn't what I want.

[isladogs]

That bit should be easy to solve
If the field is called TestScore, set the form control =Mid(TestScore,2)

[andy49]

Then I need to get back to my work computer I think. I attempted that sort of thing earlier at work and it didn't work. Many thanks again.