Windows Vista 
Access 2007
I am running an aggregate query on student and student attendance tables in which they are never absent (attendance status <> anything but present). I am using DISTINCTROW to return only one instance of the student name even though they will have many records meeting the above attendance status criteria. It works just like I need it to. I now added a from date and to date text box so that I can check perfect attendance for each quarter. When I add the attendance date field to my query and add criteria where attendance date >= from date and <= to date the DISTINCTROW doesn't work properly. My results show the same student name repeatedly. The query itself returns the correct students though. Can I not use DISTINCTROW if I am using criteria on more than one field? By the way, I removed the attendance date field from the query and it worked correctly as it did when I originally created it so it definitely has something to do with adding attendance date to the query.
Thanks for any help!
Just in case it will help, here is the query.
SELECT DISTINCTROW Student.LastName, Student.FirstName, Student.MiddleName, Student.ClassPeriod, Attendance.AttendanceDate, Attendance.AttendanceStatus FROM Student INNER JOIN Attendance ON Student.StudentID = Attendance.StudentID GROUP BY Student.LastName, Student.FirstName, Student.MiddleName, Student.ClassPeriod, Attendance.AttendanceDate, Attendance.AttendanceStatus HAVING (((Attendance.AttendanceDate)>=[forms]![PerfectAttendance]![FromDate].[Value] And (Attendance.AttendanceDate)<=[forms]![PerfectAttendance]![ToDate].[Value]) AND ((Attendance.AttendanceStatus)<>"ABSENT - EXCUSED" And (Attendance.AttendanceStatus)<>"ABSENT - UNEXCUSED" And (Attendance.AttendanceStatus)<>"TARDY - EXCUSED" And (Attendance.AttendanceStatus)<>"TARDY - UNEXCUSED" And (Attendance.AttendanceStatus)<>"REMANDED TO ALT. SCHOOL"));
Reply With Quote
