Limit to date

[Daryl2106]

Hi Folks,

I am in a little over my head with how to do this. I think it is easy but I can't figure it out...

I have a Student Report Card (ProgressReport) based on a query for all Active students (currently) enrolled. Theses students have attendance data that goes back to 2012-2013. I need to limit the calulation to AttendanceDates of <01-Sept-2013. That way the AttendanceAverage for each course currently enrolled in will only show an average for attendance data since september.

Here is the SQL. I'm not sure if it enough to make sense of my problem and question.

Thanks in advance!



SELECT ContactInformation.StudentNumber, ContactInformation.FirstName, ContactInformation.LastName, ContactInformation.Address1, ContactInformation.Address2, ContactInformation.Address3, ContactInformation.Town, ContactInformation.Province, ContactInformation.PostalCode, ContactInformation.FacultyAdvisor, ContactInformation.Active, StudentEnrollmentTable.CourseStatus, StudentEnrollmentTable.Progress, StudentEnrollmentTable.GradeToDate, StudentEnrollmentTable.Comment, CoursesNEW.[Course Title], SectionNEW.[Class Section], [LU_Instructor]![LastName] & ", " & [LU_Instructor]![FirstName] AS InstructorName, Sum(StudentAttendance1.ClassesAttended) AS SumOfClassesAttended, Sum(StudentAttendance1.ClassesOffered) AS SumOfClassesOffered, FormatPercent(Sum([StudentAttendance1]![ClassesAttended])/Sum([StudentAttendance1]![ClassesOffered]),0) AS AttAvg, ContactInformation.MiddleName
FROM (SectionNEW INNER JOIN (LU_Instructor INNER JOIN (CoursesNEW INNER JOIN (ContactInformation INNER JOIN (AnnualClassNumbers1 INNER JOIN StudentEnrollmentTable ON AnnualClassNumbers1.ID = StudentEnrollmentTable.AnnualClassNumberID) ON ContactInformation.StudentNumber = StudentEnrollmentTable.StudentNumber) ON CoursesNEW.PKCourseID = AnnualClassNumbers1.FKCourseInventoryID) ON LU_Instructor.InstructorID = AnnualClassNumbers1.FKInstructorID) ON SectionNEW.PKSectionID = AnnualClassNumbers1.FKSectionID) INNER JOIN StudentAttendance1 ON StudentEnrollmentTable.ID = StudentAttendance1.FKStudentEnrollmentID
GROUP BY ContactInformation.StudentNumber, ContactInformation.FirstName, ContactInformation.LastName, ContactInformation.Address1, ContactInformation.Address2, ContactInformation.Address3, ContactInformation.Town, ContactInformation.Province, ContactInformation.PostalCode, ContactInformation.FacultyAdvisor, ContactInformation.Active, StudentEnrollmentTable.CourseStatus, StudentEnrollmentTable.Progress, StudentEnrollmentTable.GradeToDate, StudentEnrollmentTable.Comment, CoursesNEW.[Course Title], SectionNEW.[Class Section], [LU_Instructor]![LastName] & ", " & [LU_Instructor]![FirstName], ContactInformation.MiddleName
HAVING (((ContactInformation.Active)=1 Or (ContactInformation.Active)=3 Or (ContactInformation.Active)=4));

[June7]

I don't see any field in the query for attendance dates. I expect will need this criteria in the nested GROUP BY query.

Why using GROUP BY when there are no aggregate calcs?

[Daryl2106]

Sorry June,

I had deleted that field while trying to solve my problem before I sen the SQL. Here is the SQL I was working with. I still get more attendance records being used in the calculation that just those since classes started in September. At one point I was getting the Course appearing in the Report several times (once for each attendence entry.

I think I do have aggregate calc. I sum ClassesAttended and ClassesOffered and then find a AttAvg (which is really a percent attendance).

Thanks agian for your help.

TC,

Daryl


SELECT ContactInformation.StudentNumber, ContactInformation.FirstName, ContactInformation.LastName, ContactInformation.Address1, ContactInformation.Address2, ContactInformation.Address3, ContactInformation.Town, ContactInformation.Province, ContactInformation.PostalCode, ContactInformation.FacultyAdvisor, ContactInformation.Active, StudentEnrollmentTable.CourseStatus, StudentEnrollmentTable.Progress, StudentEnrollmentTable.GradeToDate, StudentEnrollmentTable.Comment, CoursesNEW.[Course Title], SectionNEW.[Class Section], [LU_Instructor]![LastName] & ", " & [LU_Instructor]![FirstName] AS InstructorName, Sum(StudentAttendance1.ClassesAttended) AS SumOfClassesAttended, Sum(StudentAttendance1.ClassesOffered) AS SumOfClassesOffered, FormatPercent(Sum([StudentAttendance1]![ClassesAttended])/Sum([StudentAttendance1]![ClassesOffered]),0) AS AttAvg, ContactInformation.MiddleName, StudentAttendance1.AttendancePeriod
FROM (SectionNEW INNER JOIN (LU_Instructor INNER JOIN (CoursesNEW INNER JOIN (ContactInformation INNER JOIN (AnnualClassNumbers1 INNER JOIN StudentEnrollmentTable ON AnnualClassNumbers1.ID = StudentEnrollmentTable.AnnualClassNumberID) ON ContactInformation.StudentNumber = StudentEnrollmentTable.StudentNumber) ON CoursesNEW.PKCourseID = AnnualClassNumbers1.FKCourseInventoryID) ON LU_Instructor.InstructorID = AnnualClassNumbers1.FKInstructorID) ON SectionNEW.PKSectionID = AnnualClassNumbers1.FKSectionID) INNER JOIN StudentAttendance1 ON StudentEnrollmentTable.ID = StudentAttendance1.FKStudentEnrollmentID
GROUP BY ContactInformation.StudentNumber, ContactInformation.FirstName, ContactInformation.LastName, ContactInformation.Address1, ContactInformation.Address2, ContactInformation.Address3, ContactInformation.Town, ContactInformation.Province, ContactInformation.PostalCode, ContactInformation.FacultyAdvisor, ContactInformation.Active, StudentEnrollmentTable.CourseStatus, StudentEnrollmentTable.Progress, StudentEnrollmentTable.GradeToDate, StudentEnrollmentTable.Comment, CoursesNEW.[Course Title], SectionNEW.[Class Section], [LU_Instructor]![LastName] & ", " & [LU_Instructor]![FirstName], ContactInformation.MiddleName, StudentAttendance1.AttendancePeriod
HAVING (((ContactInformation.Active)=1 Or (ContactInformation.Active)=3 Or (ContactInformation.Active)=4) AND ((StudentAttendance1.AttendancePeriod) Between #1/9/2013# And #5/30/2013#));

[June7]

Okay, I found the aggregate functions, just couldn't spot them before.

Is the value in AttendancePeriod a date? Use of word 'period' makes me think of something other than a specific date. If it is a date, then don't know why the query doesn't work. It's too complex for me to follow. Would have to test with data.

Maybe trying to do too much in one query. A report with Grouping & Sorting features and aggregate calcs in footers might be better. Maybe even report/subreport.

[Daryl2106]

Thanks for the reply. AttendancePeriod is a date. (The Period refers to a two week period ending on that date.) Ah! The split report makes sense. I thought about that but didn't think a report could be split like a form could have a subform. I'll work on that tonight and send along the DB if I get stuck.

The empty ClassNumber value in the previous post is not a serious problem. I'll put it on hold for now.

Thanks again and TC,

Daryl

[Daryl2106]

Hi June,

You're right; probably trying to do too much. I decided to have two seperate reports: one for grades and the other for attendance. This seems to work well. With more experience I may be able to combine them.

Thansk so much again,

Daryl