Prev: How to look-up ID#
Next: most close return
From: Renaud_D on 4 Jul 2008 10:42
Hello,

I have a table consisting of student names linked to another one with the
date and status of passed tests (one to many relation).

It does happen that a student passes after the 1st test or needs sometimes
to take it several times before succeeding.

We have something looking like:
Student 1 - date 1st test - result 1st test (failed)
Student 1 - date 2nd test - result 2nd test (passed)
Student 2 - date 1st test - result 1sr test (failed)
Student 2 - date 2nd test - result 2nd test (failed)
Student 2 - date 3rd test - result 3rd test (failed)

I'm trying to design a query that would only return the name of the students
who didn't succeed and consequently exclude the name of those who did pass,
even if after the xth attempt.

In the above example, the query should therefore return:
Student 2
only listed once, for the clarity of the report.

Any advice would be appreciated!

Regards,

Renaud, Brussels
From: louisjohnphillips on 4 Jul 2008 12:09
On Jul 4, 7:42 am, Renaud_D <Rena...(a)discussions.microsoft.com> wrote:
> Hello,
>
> I have a table consisting of student names linked to another one with the
> date and status of passed tests (one to many relation).
>
> It does happen that a student passes after the 1st test or needs sometimes
> to take it several times before succeeding.
>
> We have something looking like:
> Student 1 - date 1st test - result 1st test (failed)
> Student 1 - date 2nd test - result 2nd test (passed)
> Student 2 - date 1st test - result 1sr test (failed)
> Student 2 - date 2nd test - result 2nd test (failed)
> Student 2 - date 3rd test - result 3rd test (failed)
>
> I'm trying to design a query that would only return the name of the students
> who didn't succeed and consequently exclude the name of those who did pass,
> even if after the xth attempt.
>
> In the above example, the query should therefore return:
>   Student 2
> only listed once, for the clarity of the report.
>
> Any advice would be appreciated!
>
> Regards,
>
> Renaud, Brussels

Try one of these methods:

SELECT A.StudentID, A.StudentName
from Students as A
where 0 = ( select count(*)
from StudentGrades
where StudentID = A.StudentID
and Result = 'Passed' )

or

SELECT A.StudentID, A.StudentName
from Students as A
where not exists
( select 'true'
from StudentGrades
where StudentID = A.StudentID
and Result = 'Passed' )


or

SELECT A.StudentID, A.StudentName
from Students as A LEFT JOIN StudentGrades as B on A.StudentID =
B.StudentID
where B.Result = 'Passed' and B.StudentID is null
From: Marshall Barton on 4 Jul 2008 12:52
Renaud_D wrote:
>I have a table consisting of student names linked to another one with the
>date and status of passed tests (one to many relation).
>
>It does happen that a student passes after the 1st test or needs sometimes
>to take it several times before succeeding.
>
>We have something looking like:
>Student 1 - date 1st test - result 1st test (failed)
>Student 1 - date 2nd test - result 2nd test (passed)
>Student 2 - date 1st test - result 1sr test (failed)
>Student 2 - date 2nd test - result 2nd test (failed)
>Student 2 - date 3rd test - result 3rd test (failed)
>
>I'm trying to design a query that would only return the name of the students
>who didn't succeed and consequently exclude the name of those who did pass,
>even if after the xth attempt.
>
>In the above example, the query should therefore return:
> Student 2
>only listed once, for the clarity of the report.


SELECT DISTINCT student
FROM table
WHERE Not Exists (SELECT X.student
FROM table As X
WHERE X.result = "passed")

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: How to look-up ID#
Next: most close return