Stuck on a Join

[betheball]

I have a table that lists 25 tests that need to be performed on a system. I have a second table that includes the results of the test. Tests are repeated until the particular test is successful. So, an item may have 3 or 4 corresponding records in the Tests table, with only one having a status of "successful". With that, here is my problem. I am trying to display a list of records from the main table, but only include the records that have yet to pass. I thought this would do it, but it returns nothing:

SELECT DISTINCT ID, testTitle, testScreen, testFile_Name FROM Results RIGHT JOIN SERPTestInput ON Results.testID = SERPTestInput.ID WHERE Results.testStatus <> 'Successful'

Now if I delete the WHERE clause, all records are returned. Any thoughts?

EDIT - The above showed nothing because evidently Access does not consider a NULL to meet the <>'Successful' criteria. The following seems to work, but I am a bit skeptical. I don't have enough test data to be sure. The first criteria makes sure I get back each item that does not yet have any data in the results table. The second criteria is designed to prevent me from getting a record that has both failed tests and a successful one. Does it look reasonable?

SELECT DISTINCT ID, testTitle, testScreen, testFile_Name, testStatus FROM Results RIGHT JOIN SERPTestInput ON Results.testID = SERPTestInput.ID WHERE ID NOT IN (Results.testID) AND ID NOT IN(SELECT testID FROM Results WHERE testStatus = 'Successful')

 

[Louie]

If Nlee is your problem why not just exclude it in the where clause?

SELECT Results.TestId, Results.[No], Results.TestStatus, Results.TestTitle, Results.TestScreen, Results.Test_FileName
FROM SerPTestInput INNER JOIN Results ON SerPTestInput.SerpTestInputId = Results.TestId
WHERE (Results.TestStatus="successful") And ( Results.TestStatus Is Not Null);

Louie ...