a tricky selection query

[Howard]

I need to select the latest of several duplicate entries.

An output from an education database has left me with a table of grades
for different subjects, entered at different times. Simplifying it a bit
the table would look like this.

STUDENT_NAME, SUBJECT, DATE_OF_GRADE, GRADE
jim maths 1st Jan 2007 B
sally English 6 feb 2007 C
jim maths 10 March 2007 A
jenny French 5th July 2007 D
sally English 3rd July 2007 E
jim maths 5th feb 2007 F


I just want the latest grade for each student and subject i.e.

jim maths 10 March 2007 A
sally English 3rd July 2007 E
jenny French 5th July 2007 D


How can I generate this?

Howard

 

[rolaaus]

Assuming that each grade entry, or semester/quarter, whenever the grades are
entered, are at the same time for each class, you can sort the query by
Date_of_Grade and take the First instance, sort of like

Select Student, subject, grade, first(grade_date) from tbl_Grades order by
grade_date group by student, subject, grade, grade_date

 

[Howard]

Assuming that each grade entry, or semester/quarter, whenever the grades are
entered, are at the same time for each class, you can sort the query by
Date_of_Grade and take the First instance, sort of like

Select Student, subject, grade, first(grade_date) from tbl_Grades order by
grade_date group by student, subject, grade, grade_date

Thanks,
Tried that and got 'You tried to execute a query that does not include
the specified expression 'ID' as part of the aggregate function' error

(ID is just an autonumber key)

Any ideas?

Howard

 

[rolaaus]

This generally occurs when you are running a total query and have somehow not
included a group by or summary field (typically written manually by hand
instead of using the Access query deign GUI).

In other words, for every field in your select query, you either need to
include that field in the Group By clause, or use some expression (ie. Sum,
Avg, First, Last, Min, Max, etc.)

I can assume from your sample data, and the error stating some sort of ID,
that this is likely a student ID, so include it in the Group By - I include
the above info so you know what is likely the problem if it ever happens
again. For example, if you wanted a GPA by Semester, you would include
everything already posted in my SQL example, but add the AVG function to the
Grade

Select Student, subject, Avg(grade) as GPA, grade_date as Semester from
tbl_Grades order by grade_date group by student, subject, grade_date

(Now that I look at the SQL statement closer, I may have included Grade_date
in the Group By when I shouldn't have (because it is using the Expression
FIrst(Grade_date) - but I might be wrong, I can't double-check without
creating some similar table structure in my own Access file and designing the
query too.

 

[John Spencer]

You can use an embedded subquery in the FROM clause to get the data

SELECT S.StudentID, S.Subject, S.Grade, S.Date_of_Grade
FROM YourTable as S INNER JOIN
(SELECT Max(Date_Of_Grade) as LastGradeDate
, StudentID, Subject
FROM YourTable as Tmp
GROUP BY StudentID, Subject
) as T
ON S.Date_of_Grade = T.LastGradeDate
AND S.StudentID = T.StudentID
AND S.Subject = T.Subject

If this fails or is too complicated do this in two steps
First query - saved as qLastGrade
-- Add StudentID, Subject, Date_of_Grade to the query
-- Select View: Totals from the menu
-- Change GROUP BY to MAX under Date_Of_Grade

Second Query
-- Add your table and the query qLastGrade to the query
-- Set joins between StudentID, Subject, and the Date fields
(Drag from StudentId to StudentID, etd)
-- Add the fields from your table that you want to see
-- Run the query

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..