I've seen several posts on this idea but none quite seems to have the answer for me.
Every student at one exam slot sits three tests giving a total out of 240. The grades (from 1-,1,1+,2-,2,2+ up to 9) are at different scores for each assessment.
So this crosstab produces the total score from the three papers in a calendar slot such as Christmas 2017.
I have
Code:
SELECT qryEnterResults.slotID_FK, qryEnterResults.MathsClass, qryEnterResults.StudentID_PK, qryEnterResults.Firstname, qryEnterResults.surname, qryEnterResults.PaperNumber, Min(qryEnterResults.TestScore) AS MinOfTestScore, Sum(qryEnterResults.TestScore1) AS TotalScore
FROM qryEnterResults INNER JOIN tblCalendar ON qryEnterResults.SlotID_FK = tblCalendar.SlotID_PK
WHERE (((qryEnterResults.slotID_FK)=17))
GROUP BY qryEnterResults.slotID_FK, qryEnterResults.MathsClass, qryEnterResults.StudentID_PK, qryEnterResults.Firstname, qryEnterResults.surname, qryEnterResults.PaperTier, qryEnterResults.UPN, qryEnterResults.PaperNumber;
which works fine producing a list of names, other details and exam results out of 240 for the exam slot 17 (Christmas 2017).
eg
John 200
Simon 198
The score column needs to be converted to a grade and this is where the issue lies.
I have (and will have more) 4 grade tables
score_ID as autonumber
score as short (from 1-240)
grade as string (1-,1,1+ etc all the way up to 9+)
Each assessment needs to be linked to one of the tables but not the same one. It will vary depending on the test they did.
The following:
Code:
SELECT qryEnterResults_Crosstab.slotID_FK, qryEnterResults_Crosstab.StudentID_PK, qryEnterResults_Crosstab.mathsclass, qryEnterResults_Crosstab.Firstname, qryEnterResults_Crosstab.surname, IIf([papertier]='Higher',[gradeH],[gradef]) AS grade
FROM qryEnterResults_Crosstab LEFT JOIN TblBoundaryMockSet3Autumn2017 ON qryEnterResults_Crosstab.TotalScore = TblBoundaryMockSet3Autumn2017.score;
produces a list of names and their grade based on the grade table TblBoundaryMockSet3Autumn2017 (which will vary)
but for this form I'll need ALL the results from every assessment connected to the correct grade table.
Can I join the crosstab to the grade table using SQL for each slot, then union those results together?
If not, can anybody see a work around?
Happy to produce other info if you need it.