average query - how can it be so difficult? :-(

[ymds]

hey everyone, please try to help a little.

i have DB with 2 tables:
1).grades- the columns are: id, student_name, grades, school_year,
2).students-the columns are: id, student_name

of course in the grades table student name repeats more than once (1-to-many relationship).

i need a query that show the max average for every student grades. for example: dan gained the following grades: 80, 60, 98.


grace gained: 60, 60,70.

is there any way to create a query that will calculate the averages for every student and finally show the max average?
(dan grades average is 79, while grace's is 63.3
it is very important that all the grades will be in one column, so i can't split the tables in other way..
i need to write a query that will show the max average (which in that case is 79) and the student name that has that average.
is it possible in someway to do it?

tnx a lot!!!

(ps. the DB is attached - i'll be grateful for any example)

[maximus]

My Table2 Contains studentId Linked to StudenID in Table1 (One to many Relationship), Grades, Year.

This is the sql for the Average query.

SELECT TOP 1 [Table2].[StudentID], Avg([Table2].[Grades]) AS AvgOfGrades, [Table2].[SchoolYear]
FROM Table2
GROUP BY [Table2].[StudentID], [Table2].[SchoolYear]
ORDER BY Avg([Table2].[Grades]) DESC;

Refer to Query Table2 Query in the attached sample.

[ymds]

hey' thank u so so much!
do u have any idea how to create another column with autoNumbers so i'll be able to see what is the place of every student?
tnx!!!