Help! I'm about to give up on Access altogether-- my reports are missing records in a pretty unpredictable fashion. Here's what's going on
Access 2007
I have a report that is grouping by Class and listing Students for each class. I'm using the Group & Sort feature to Group on ClassID. The ClassID Header lists the ClassName and the Detail of the report is listing each individual student.
I have classes for each day of the week, but when I run the report it is clearly missing all Friday classes (there are 3.) They are just on there. I load up the Record Source and view in Datasheet view and the Friday classes are listed there. To further test, I use a criteria in SQL Design View to only show Friday classes, and when I rerun the report it shows the Friday classes. What? However, that doesn't even work properly. It only lists one student for the last class on the page, even though there are definetely more than one student in that class.
I also changed the 'keep whole group together'/'do not keep together' settings in the Group & Sort box. Sometimes it will remove the entire last Friday class altogether. Why doesn't it make the second page of the report ton include the rest of the data? I have not checked the other classes to see if people are missing, but now I'm nervous that my reports are randomly missing data.
Here's the Record Source query:
SELECT Classes.Day, Classes.ClassTimeBegin, [LastName] & ", " & [FirstName] AS LastFirst, Student_Class.Student_ClassID, Student_Class.ClassID, Classes.ClassName, Student_Class.StudentID
FROM Students INNER JOIN (Classes INNER JOIN Student_Class ON Classes.ClassID = Student_Class.ClassID) ON Students.StudentID = Student_Class.StudentID
ORDER BY [LastName] & ", " & [FirstName];
I don't think there's anything wrong with the query (as the Datasheet view shows all the data)- it appears to be problems with grouping and pagination. Any help or insight would be greatly appreciated.