CODEWITHSUNDEEP
ms-accessms-access-2010ms-access-2013
FSm
FSm

Reputation: 2057

Query for students passed and not passed

How to make a query to know whether a student passed or not, where each student has ten subjects and the pass grade for each subject is (>49)
Student to be passed, he/she has to pass in all the subjects. Any help please ?

Upvotes: 0

Views: 1645

Answers (1)

HansUp
HansUp

Reputation: 97101

Use a GROUP BY query to count the number of subject failures for each student:

Count(IIf(q.Total > 49, Null, 1))

Ultimately you want to see only the students who have passed all their subjects, which means their count of failures is zero. So include that condition in the query's HAVING clause.

SELECT
    q.StudentsID,
    Count(IIf(q.Total > 49, Null, 1)) AS CountOfFailures
FROM QueryTotalGraedFirstCourse AS q
GROUP BY q.StudentsID
HAVING Count(IIf(q.Total > 49, Null, 1)) = 0;

You can start building this query in the Access query designer's Design View. Specify your table, select the fields, and set up the basic GROUP BY details in Design View. Then you can switch to SQL View to include the IIf expression in the Count.

Here is my sample data for QueryTotalGraedFirstCourse. To keep it simple I only included 2 subjects per student. 

StudentsID subject_id Total
---------- ---------- -----
         1          1    90
         1          2    85
         2          1    65
         2          2    30
         3          1    40
         3          2    35

Based on your description, "passed" means no failing score for any subject, so only StudentsID 1 from my sample data passed. This is the result from my query, using the data sample, and tested in Access 2010:

StudentsID CountOfFailures
---------- ---------------
         1               0

If you want to see all students, not just those who passed, simply remove the HAVING clause.

Upvotes: 1

Related Questions