Help Required To Develop query

[mubi_masti]

Hi

I need help regarding developing a query.

I have two tables both are linked with each other. One table contains different students while other table contains the result of different activites of each student.

I want to extract the records of only those students who were "absent" or who were asked to "repeat" their activity but still they are not declared "Pass" or "Fail" e.g

new080592006 07-Oct-11 Absent
new080592006 10-Oct-11 Repeat
new080592006 11-Oct-11 Pass

new080592008 07-Oct-11 Repeat
new080592008 10-Oct-11 absent

I need a query result in which only one record appear in std_id = new080592008

 

[MSAccessRookie]

If you build the Query to show any StudentID with Activity Status of "Absent" Or "Repeat" it will also include any StudentID that also has an Activity Status of "Pass".

SELECT StudentID, ActivityDate, ActivityStatus
FROM YourTable
WHERE ((ActivityStatus="Absent") Or (ActivityStatus="Repeat"))

To eliminate the "Pass", you will need to modify the WHERE Clause.

WHERE ((ActivityStatus="Absent") Or (ActivityStatus="Repeat"))
AND (StudentID NOT IN (SELECT StudentID FROM YourTable WHERE ActivityStatus="Pass"))

 

[mubi_masti]

Great!!!!

It really works and solve my problem. I really appreciate your approach. I am new in using MS Access

Thanks a lot