Compare data of column in many fields.

  • Thread starter Thread starter triann_a
  • Start date Start date
T

triann_a

I need your help please.

I need to create a query that will allow me to compare a students score from
multiple scores of one test and identify which is the highest and note it in
another column.

The fields are;

Student Name
Student ID
Course Title
Course ID
Activity Date
Score
Report Score (I think this is the column that should be used to identify the
students igest score)
Any input that can be offered will be greatly appreciated.
 
This will do it for multiple students ---
SELECT Q.[Student ID], Q.[Course ID], Q.Score, (SELECT COUNT(*) FROM
TableName Q1
WHERE Q1.[Student ID] = Q.[Student ID] AND Q1.[Course ID] = Q.[Course
ID]
AND Q1.Score >= Q.Score) AS Rank
FROM TableName AS Q
WHERE ((((SELECT COUNT(*) FROM TableName Q1
WHERE Q1.[Student ID] = Q.[Student ID] AND Q1.[Course ID] = Q.[Course
ID]
AND Q1.Score >= Q.Score))=1))
ORDER BY Q.[Student ID], Q.[Course ID], Q.Score DESC;
 
Back
Top