Ranking problem

[Tanya]

I have been scouring the Internet looking for a solution to my problem and have attached the closest I can get to the desire outcome. If anyone can help I would really appreciate it.


-----My first query [qryMITotals] totals scores in a multiple intelligence test

SELECT tblStudents.StudentID, [VL1]+[VL2]+[VL3]+[VL4]+[VL5]+[VL6]+[VL7]+[VL8] AS [Verbal/Linguistic], [LM1]+[LM2]+[LM3]+[LM4]+[LM5]+[LM6]+[LM7]+[LM8] AS [Logical/Mathematical], [VS1]+[VS2]+[VS3]+[VS4]+[VS5]+[VS6]+[VS7]+[VS8] AS [Visual/Spacial], [IN1]+[IN2]+[IN3]+[IN4]+[IN5]+[IN6]+[IN7]+[IN8] AS Interpersonal, [MU1]+[MU2]+[MU3]+[MU4]+[MU5]+[MU6]+[MU7]+[MU8] AS Musical, [NA1]+[NA2]+[NA3]+[NA4]+[NA5]+[NA6]+[NA7]+[NA8] AS Naturalist, [BK1]+[BK2]+[BK3]+[BK4]+[BK5]+[BK6]+[BK7]+[BK8] AS [Body/Kinesthetic], [IR1]+[IR2]+[IR3]+[IR4]+[IR5]+[IR6]+[IR7]+[IR8] AS Intrapersonal
FROM tblStudents INNER JOIN [tblMI Quiz Data] ON tblStudents.StudentID = [tblMI Quiz Data].StudentID;

--- My second query A union query [qryMIUnion] makes sense of first query, providing studentID, Multiple Intelligence and score

SELECT StudentID, "Verbal/Linguistic" AS Skill, [Verbal/Linguistic] AS Score FROM qryMITotals
UNION ALL SELECT StudentID, "Logical/Mathematical" AS Skill, [Logical/Mathematical] AS Score FROM qryMITotals
UNION ALL SELECT StudentID, "Visual/Spacial" AS Skill, [Visual/Spacial] AS Score FROM qryMITotals;
UNION ALL SELECT StudentID, "Body/Kinesthetic" AS Skill, [Body/Kinesthetic] AS Score FROM qryMITotals;
UNION ALL SELECT StudentID, "Musical" AS Skill, [Musical] AS Score FROM qryMITotals;
UNION ALL SELECT StudentID, "Interpersonal" AS Skill, [Interpersonal] AS Score FROM qryMITotals;
UNION ALL SELECT StudentID, "Intrapersonal" AS Skill, [Intrapersonal] AS Score FROM qryMITotals;
UNION ALL SELECT StudentID, "Naturalist" AS Skill, [Naturalist] AS Score FROM qryMITotals;

----the third query which I don't fully understand [qryRank] Ranks all multiple intelligence scores and herein lays my problem********

SELECT a1.StudentID, a1.Skill, Count(a2.Score) AS Rank
FROM qryMIUnion AS a1, qryMIUnion AS a2
WHERE (((a1.Score)<=[a2].[Score] Or (a1.Score)=[a2].[Score]))
GROUP BY a1.StudentID, a1.Skill, a1.Score;

I want to be able to identify the rank order of multiple intelligence score for each studentID.

i.e. StudentID MI Score Rank
1234567 BodyKinesthetic 30 1
1234567 Naturalist 27 2
2345689 Logical/mathe 22 2
2345689 Naturalist 27 1

Thanks in advance

Tanya

 

[redneckgeek]

This query will rank each student by skill. Embed it in another query that allows you to pull out each student.

SELECT a1.StudentID, a1.Skill, a1.Score, 
   (select Count(*) 
     FROM qryMIUnion a2 
     WHERE a2.skill=a1.skill and a2.score>a1.score)+1 AS rank
FROM qrymiunion AS a1
ORDER BY a1.Skill, a1.Score DESC;

 

[Tanya]

I ran your code in place of my third query, but didn't get the desired ranking.
Also, I am not sure what you mean about embeding it in another query? Could you please be more specific.

 

[redneckgeek]

SELECT * 
FROM (
     SELECT a1.StudentID, a1.Skill, a1.Score, 
         (select Count(*) 
           FROM qryMIUnion a2 
            WHERE a2.skill=a1.skill and a2.score>a1.score)+1 AS rank
     FROM qrymiunion AS a1
     ORDER BY a1.Skill, a1.Score DESC)
WHERE STUDENTID='123456'

 

[Tanya]

Thanks... I tried this [adding where studentid = '123456'] and it didn't work and thought you may have been referring to another way of scripting this.

cheers
Tanya

 

[redneckgeek]

Well, I don't really rememeber off the top of my head - does your data really contain a student number 123456? I was hoping you'd insert the right number there. The data shouldn't be enclosed in quotes, either. That may give you a "type mismatch" error.