Excluding values from a query

[solnajeff]

Hi

I am trying to create a query that will show only trainees who have not completed a test.

The attached relationship produces a query that does the opposite, i.e. only trainees from a particular school who have completed a specified test.

I cannot work out how to exclude trainees rather than include them.

I would appreciate any advice.

Regards


Jeff

 

[arnelgp]

you can use SQL view:

select tblTrainee.* from tblTrainee Left Join tblTestResults on tblTestResults.TraineeId = tblTrainee.TraineeID Where TblTestResult.TraineeID Is Null;

 

[solnajeff]

Hi

Thanks for the advice, unfortunately I cannot get it to work.

I have made up a sample to illustrate my problem and the query returns the records showing completed tests matching the criteria. However I am trying to find out how to show those trainees who have not yet completed the relevant test, i.e the inverse of the result I have.

Unfortunately I can only get it to show zero records.

Regards

Jeff

 

[jdraw]

Try this query-- what arnelgp advised

SELECT tblTrainee.TraineeID, tblTrainee.SchoolID, tblTestResult.TraineeID
FROM tblTrainee LEFT JOIN tblTestResult ON tblTrainee.TraineeID = tblTestResult.TraineeID
WHERE (((tblTestResult.TraineeID) Is Null));

 

[solnajeff]

Hi

Thanks for the reply.

I finally managed to make it work but did need to use an additional query because the result table requires a specific Test ID and Test Number.

Once again many thanks

Regards

Jeff

 

[jdraw]

Can you describe in simple English (not SQL and not database terms) what is the purpose of this proposed query?