Compare data of column in many fields.

[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.

 

[raskew via AccessMonster.com]

Hi -

This assumes:
a. There's just one student's info in the table. If not, please post
back.
b. Field Score is a number and not a text field.

Copy/paste to a new query, replacing table name as necessary.

SELECT
TOP 1 TableName.Score as ReportScore
, TableName.*
FROM
TableName
ORDER BY
TableName.Score DESC;

HTH - Bob

 

[KARL DEWEY]

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;