Return records not in subquery 2

[MajP]

I have three queries that return a student ID, a test ID, and a test status. These tests can be taken many times so the student may have many records where he/she passed the test, have "verified credit" (less than passing), be scheduled, failed, etc. I want to eventually make a join query from three queries. First I get a distinct record for each test a student passed:

qryPass
studentID
testID
testStatus

then a qry for every student that has verified credit for a test

qryVC
same fields as above but returns records where testStatus is "VC"

and then a query returning the tests that a student is scheduled to take.

qrySched
same fields as above, but testStatus is "Scheduled".

This is where I need help. I want to make a qry (call it qryVCandNotPassed) of all the student's test that have verified credit, but have not passed the test yet. So I want to return all records from

qryVC where the (studentID AND testID) are not in qryPassed

I have tried several subqueries but either get some cartesian product, no records, or all records.

If I can get that one right I should be able to chain the next one; student who have a test scheduled and have not passed or gotten VC.

I did this originally using a function in a calculated field which was simple worked correctly, but was really slow.

Thanks.

 

[CaptainD]

If I understand your post, you have just one table but I'm not clear on what you want for a results set since all you do is show the names of three queries and not the queries themselves.

I assume the field "testStatus" holds different values that you are trying to separate.

How does your table show someone has "CV" and "Not passed"?

I would look into doing a "Self Join". Posting more information about your table and values will help.

 

[MajP]

I have one table that I store all the records for test taken by each student. This table is a join (link) table since I have a many to many relationship between students and tests.

joinTblStudents_Tests
studentID
testID
dtmTestDate
intScore
testStatus

There are 11 different Standards of Learning (SOL) that each student must take. For example "World History I". A student may have multiple records in the join table with different test status. For example 2 Fail attempts, then a Pass. Another student may have a VC and then "Scheduled" to take another test.

Before I took a cartesian product of the Student table and the Test table which gave me every combination of students and tests. For example 100 students, 11 required tests, and therefore 1100 records showing each student and each test. Then I built a function that I passed a student ID and a test ID for every record. The function searched the record of all tests taken ("joinTblStudents_Tests"). If there was a record with "Pass" for that student/test combo it returned pass as the overall summary, if no Pass it returned VC, if no VC it returned "Scheduled", if no "Scheduled" record it returned "Fail", and if no record returned "not attempted". This worked fine and showed how far along each student was with each test, but it was real slow.

So now I am trying to do the same thing with queries. If I can get the individual queries, I can join them together and link to my cartesian product.

So I want a qry that shows all test that students have passed.

qryPassed

That is a simple select. I also have another simple select qry that shows all records with VC, qryVC. However, if they eventually passed the test then I do not really care that at some time they were VC. So now I need a new qry that returns all records from qryVC not in qryPassed.

qryPass
studentID
testID
testStatus

qryVC
studentID
testID
testStatus

data in qryPass looks like

1 11 Pass
3 12 Pass
4 10 Pass

data in qryVC looks like

1 11 VC
3 13 VC
6 14 VC

I want to return only
3 13 VC
6 14 VC

because studetn 1 eventually passed test 11.

Hope that helps

 

[CaptainD]

This work

SELECT StudentID, TestID, TestStatus 
FROM tTestStatus
WHERE TestID NOT IN (SELECT TestID FROM tTestStatus WHERE TestStatus = "Pass");

 

[JoeAtWork]

CaptainD - I don't think that will work because it doesn't take into account whether a specific student passed (i.e. if any student passed then that test will be filtered out for all students).

I'm not sure if the following syntax is valid, but something like

SELECT StudentID, TestID, TestStatus 
FROM tTestStatus ts
WHERE TestID NOT IN (SELECT TestID FROM tTestStatus WHERE TestStatus = "Pass" AND StudentID=ts.StudentID);

 

[CaptainD]

JoeAtWork,

You are correct, I created a simple table and ran my code vs yours and mine did omit some of the records. I ran yours and all the records I expected were returned.

Good catch

 

[PHV]

a new qry that returns all records from qryVC not in qryPassed
SELECT A.studentID, A.testID, A.testStatus
FROM qryVC AS A LEFT JOIN qryPassed AS B ON A.studentID=B.studentID AND A.testID=B.testID
WHERE B.studentID Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Thanks Gents. Now I was thinking maybe there is a simpler way. Assume my test status table looks like this with records for student 1 and tests 11 and 12 .

studentID testID status
1 11 Pass
1 11 VC
1 11 fail
1 11 fail
1 12 VC
1 12 fail

What I want to return is
1 11 Pass
1 12 VC

because these are the highest status for each test.
I was thinking I could add a new field "statusPrecedence"
1 for pass
2 for VC
3 for fail
4 for scheduled

Is there a way to select the top status from each studentID-testID group?

 

[CaptainD]

Like this?

SELECT StudentID, TestID, TestStatus, MAX(StatusPrecedence) as [Precedence]
FROM tTestStatus AS ts
WHERE TestID NOT IN (SELECT TestID FROM tTestStatus WHERE TestStatus = "Pass" AND StudentID=ts.StudentID)
GROUP BY StudentID, TestID, TestStatus;

 

[PHV]

SELECT studentID, testID, Choose(Min(statusPrecedence),'pass','VC','fail','scheduled') AS HighestStatus
FROM joinTblStudents_Tests
GROUP BY studentID, testID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CaptainD]

PHV,

I've never seen the Choose() Function. Thanks

It also points out an error on my solution, I should of looked for the Min() not Max()for the highest "Precedence"

SELECT StudentID, TestID, TestStatus, MIN(StatusPrecedence) AS Precedence
FROM tTestStatus AS ts
WHERE TestID NOT IN (SELECT TestID FROM tTestStatus WHERE TestStatus = "Pass" AND StudentID=ts.StudentID)
GROUP BY StudentID, TestID, TestStatus;

 

[MajP]

Thanks everyone.