Max or Top Date subquery

[liddlem]

HI folks
I have a table where student attendance history is recorded.
In order to update a student attendance for today, I would like to select their latest record (from yesterday or Friday if today happens to be a Monday) and assign the same attendance status that they had the last time that the role was taken.

So I have the following query.

SELECT Tbl_SchoolAttendance.ClassID, Tbl_SchoolAttendance.StudentID, Tbl_SchoolAttendance.AttendDate, Tbl_SchoolAttendance.SchoolAM, Tbl_SchoolAttendance.SchoolPM, Tbl_SchoolAttendance.AttendDate AS LastDt
FROM Tbl_SchoolAttendance
WHERE Tbl_SchoolAttendance.AttendDate =
(SELECT MAX(Tbl_SchoolAttendance.AttendDate) FROM Tbl_SchoolAttendance as LastDate)
ORDER BY Tbl_SchoolAttendance.ClassID, Tbl_SchoolAttendance.StudentID, Tbl_SchoolAttendance.AttendDate;

The problem is that this returns ALL the attendance records.
I only want the records that have the highest AttendDate.
I have also tries to use the TOP function as in
(SELECT TOP 1 (Tbl_SchoolAttendance.AttendDate)
but this also returns ALL records.

Any ideas?
(Access 2010)

 

[plog]

I find its always easier to create sub-queries as distinct queries and use them that way, instead of jamming them inside another SQL statement. So what I would do is just that--create a query that gets the latest attendance date for each student.

However, you're issue isn't as simple as doing that because of the other fields you are brining in. You need to restate what you want based on all those other fields. Specifically ClassID. Do you want the last attendance status of a student for that ClassID?

From the fields I see it seems a student can have multiple attendance stati on the same day. For example, they were there for morning classes, but left in the afternoon. Which status do you want since they were both absent and present on that day. This might be where ClassID comes into it.

If you could post some sample data from your table along with what results you want returned based on that sample data it would shed more light on this.