Filtering a report

[grenee]

Good Morning All,

I have a report with lists of students and their scores. Each student does 8 different subjects. My report has a calculated field which tallies the number of subjects each student passes. So the report shows 2 things - Student name and number of subjects passed. I however want only those students that have passed more than 6 subjects.

Can this be done?

Thanks in advance

 

[pr2-eugin]

What is the Recordsource of your report? You can use the HAVING Clause of the Query (if your report depends on the Query) to limit only the ones that have >= 6.

 

[grenee]

Where can I find example of Having Clause on the internet?

 

[grenee]

Do I write the Having Clause in the query or in the report?

 

[vvariety]

if your report is based on a query you can put the >=6 in the criteria of the Passed column

 

[grenee]

I am having difficulty applying this instruction; 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 name I gave before were fictitious to keep my question simple. The following however is actual:

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"));

 

[vvariety]

Here is an example found in the Help File of Access...
SELECT CategoryID,
Sum(UnitsInStock)
FROM Products
GROUP BY CategoryID
HAVING Sum(UnitsInStock) > 100 And Like "

You can try HAVING dCount([comment1],"Table Name","[comment1] = Pass") >= 6

 

[jdraw]

For any/all SQL syntax you can visit W3Shools SQL syntax and examples