Update records query...new to sql

[hithere]

Hi. I am very new to programming and sql. Right now I am creating a gradebook to hold student's courses, grades, and teachers.



Students may be able to retake a subject and thereby receive a different grade than first obtained. I would like to create a query that shows only the most recent grade a student received for any subject.

I have the following query to do such for Social Studies:

SELECT tbl_SocialStudies.ID, tbl_SocialStudies.Name, tbl_SocialStudies.Score, tbl_SocialStudies.Date
FROM tbl_SocialStudies RIGHT JOIN [SELECT Name, Max(Date) AS Latest FROM tbl_SocialStudies GROUP BY Name]. AS Recent ON (tbl_SocialStudies.Name=Recent.Name) AND (tbl_SocialStudies.Date=Recent.Latest);

Which runs fine. However, for some reason this keeps switching to:
SELECT tbl_SocialStudies.ID, tbl_SocialStudies.Name, tbl_SocialStudies.Score, tbl_SocialStudies.Date
FROM tbl_SocialStudies RIGHT JOIN [SELECT Name, Max(Date) AS Latest FROM tbl_SocialStudies GROUP BY Name; ] AS Recent ON (tbl_SocialStudies.Name=Recent.Name) AND (tbl_SocialStudies.Date=Recent.Latest);

In the GROUP BY clause Name]. keeps jumping to Name;] after the query is saved. I have to keep fixing it everytime I want to see the results.

Is there a particular reason why this is happening.

[pbaldy]

I would simply leave it as two queries instead of using the subquery:

http://www.baldyweb.com/LastValue.htm

Access is trying to "help" you, and as is sometimes the case it messes it up. I've seen it do this to subqueries before.

By the way, you don't have separate tables for each course, do you?

[hithere]

Yea, I have seperate tables for each course because some students may not be enrolled in every course.

[pbaldy]

That is a normalization mistake IMO. How would you determine which courses I have taken?

You have a situation for a classic many-to-many junction table (a course can be taken by many students, a student can take many courses). I would have a single table with fields for student ID, course ID, date and grade. That would be in addition to a students table and a courses table.

[hithere]

Thanks for your insight. I made the changes you suggested and this gave me a partial answer. What I would like is to be able to view the most recent grades for each student for each subject. What I have now is the most recent grade irregardless of what the subject is. Is it possible to acheive this?

I have included the database for reference.

[alansidman]

Try this:

SELECT tbl_Combo.Name, tbl_Combo.Subject, Max(tbl_Combo.Score) AS MaxOfScore
FROM tbl_Combo INNER JOIN qryMaxDate ON (tbl_Combo.Date = qryMaxDate.MaxDate) AND (tbl_Combo.Name = qryMaxDate.Name)
GROUP BY tbl_Combo.Name, tbl_Combo.Subject;

[hithere]

Fabulouso! Is there anyway I can append the dates to the scores as well?