Filtering a report

[George]

Good morning all.

I have a calculated field in my report which is the sum of scores for students i.e. each student does 8 subjects and my calculated field tallies how many each student has passed. so the report shows a list of students and the number of passed subjects. Now I want to show only those who have passed 7 or more subjects. Can such a filter be done? It is obvious I can't use the query since the summary calculate field does not exist in the query.

would be very grateful for an answer.

[ranman256]

Put it in your rpt query. Q1 can be the sum of each student.
Q2 can be the query of data on the report
Join Q1 and Q2 and show only those count > 7

[George]

Thanks but the explanation is a little above me. Let me be a little more specific.
In my table there are 3 fields: "StudentName", "Course" and "Score". In my corresponding Query Q1 has the same 3 fields plus another calculated field called "Result" that can have string values of either Pass or Fail. Each student does 10 subjects; therefore a student can pass a maximum of 10 subjects. The report based on this query Q1 displays "StudentName" field and a calculated field "Total Passed" giving the total number of courses each student passed. My challenge is to display only those who have passed all 10.

[June7]

Consider:

SELECT StudentName FROM Q1 WHERE Result = "Passed" GROUP BY StudentName HAVING Count(*) = 10;

[George]

Thanks for the quick response; but where should I write this query - in the query or in the report?

[June7]

That could be the RecordSource for report.

Could do as ranman suggested. If you need more info than just student name, join the suggested query to a table or query that has other data.

Make that the report RecordSource.

[George]

I am having difficulty applying your advise; I therefore have attached my actual query so you can show me how to append your suggestion. Please bear in mind that all the field names and query names I gave before were fictitious to keep my question simple. The following however is actual and was generated be Access itself:

SELECT [last Name] & " " & [First Name] AS Name, Programs.[Prog Name], [Class by Course].Group, Attendance.CourseMark, Attendance.Absents, Attendance.ExamMark, [Class by Course].Location, Nz([CourseMark])+Nz([ExamMark]) AS Total, [Class by Course].CourseMarksAllocated, [Class by Course].ExamMarksAllocated, IIf([Class by Course]![CourseMarksAllocated]=True And [Class by Course]![ExamMarksAllocated]=True,[Attendance]![CourseMark]*60/100+[Attendance]![ExamMark]*40/100,IIf([Class by Course]![CourseMarksAllocated]=True And [Class by Course]![ExamMarksAllocated]=False,[CourseMark],IIf([Class by Course]![CourseMarksAllocated]=False And [Class by Course]![ExamMarksAllocated]=True,[ExamMark]))) AS Remarks, IIf([Remarks]>49,"Pass") AS [Comment 1], Attendance.Note, Courses.[Course Name], IIf([Remarks]>40 And [Remarks]<50,"Supplimental",IIf(Nz([Remarks])<41,"Repeat","")) AS [Comment 2], NumberOfCoursesPerProgram.Number, [Class by Course].[Year Level], [Class by Course].[Semester/Session], [Class by Course].Year, Attendance.Exemption, [Class by Course].[Instructor ID]
FROM (Courses INNER JOIN (Programs INNER JOIN (Student INNER JOIN ([Class by Course] INNER JOIN Attendance ON [Class by Course].ClassbyCourseID = Attendance.[Class by course ID]) ON Student.[Student ID] = Attendance.StudentID) ON Programs.[Program Code] = [Class by Course].[Program Code]) ON Courses.[Course ID] = [Class by Course].[Course ID]) INNER JOIN NumberOfCoursesPerProgram ON (Programs.[Program Code] = NumberOfCoursesPerProgram.ProgramCode) AND (Programs.[Program Code] = NumberOfCoursesPerProgram.ProgramCode)
WHERE (((Programs.[Prog Name])="Nursing Auxiliary Studies.Cert"));

[June7]

And what is the difficulty? What happens? Does that query work? Is one of those joins to the aggregate query?

That is a complicated query. I am not sure all INNER JOINs is appropriate.

I would have to review the database.