Show records based on value

adrianc · #1 (**permalink**) 04-24-12, 14:48

Hi everyone,

I'm going to get straight to it. Basically, what I need to do is show records based on another field.

The scenario:

I have a list of employees, the employees have attended many courses (there are different courses). The employees have either passed or failed the course. I need to create a report that shows only those that have failed 3 or more courses. The calculation has to be based on a yes/no field. I am guessing that a query has to be made, however I don't know how to go about it

.

Any ideas?

r937 · #2 (**permalink**) 04-24-12, 15:23

start by showing us a query that retrieves all students and all their courses

gvee · #3 (**permalink**) 04-24-12, 15:26

Query to show number of fails for each person [that has failed a test]

Code:

SELECT person
     , Count(*)
FROM   courses
WHERE  pass = 0
GROUP
    BY person

To limit this query to show only those who have 3 or more failures add this to the end

Code:

HAVING Count(*) >= 3

r937 · #4 (**permalink**) 04-24-12, 16:30

george, come on, you have to make it just a ~wee~ bit harder for these n00bs to get their homework assignments completed

adrianc · #5 (**permalink**) 04-24-12, 17:35

This works, thank-you, however for the last part, I get the error 'Characters found after end of statement.

Thanks again

Quote:

Originally Posted by gvee

Query to show number of fails for each person [that has failed a test]

Code:

SELECT person
     , Count(*)
FROM   courses
WHERE  pass = 0
GROUP
    BY person

To limit this query to show only those who have 3 or more failures add this to the end

Code:

HAVING Count(*) >= 3

adrianc · #6 (**permalink**) 04-24-12, 17:58

No worries, found that I forgot to remove the ';'

Thank you soo much

Internet Services

Web Development

Digital Marketing

Consumer Tech