Query Criteria help needed

[iuianj07]

Hello guys,

I have a query that for example---



I have this Indexing_Loan_Status table:

Situs_ID Analyst InStChID
298 user 1
298 user 10
298 user 11
303 user 1

And I have a query that whenever a Situs_ID have InStChID = 10, then the whole Situs_ID shouldn't show up, the SQL is:

SELECT T1.Situs_ID, T2.Analyst, T2.InStChID
FROM Job_Tracking As T1
LEFT JOIN Indexing_Loan_Status As T2
ON T1.Situs_ID = T2.Situs_ID
WHERE T1.Situs_ID Not In
(SELECT DISTINCT T3.Situs_ID
FROM Indexing_Loan_Status As T3
WHERE T3.InStChID = 10)
And T2.Analyst = getuserName()


although I am trying to add something and couldn't figure out...

So basically what this query does is if the InStChID = 10, then that Situs_ID 298 shouldn't appear anymore... but when I add another record for Situs_ID 298 with InStChID = 11, then I would want it to show up in the query again... then... when another record is added for Situs_ID 298 with InStChID = 16, then it should not appear on the query again...

I know it may be confusing, so I will try to explain the query:

I am working in a Job Tracking database... and what this query does is it returns a set of records that the user has worked on the Situs_ID... the InStChID is the "status" of each record, where InStChID = 10 actually means "Indexing (job is called) Sent to Client", and so when we create this status then it should not show up anymore in the query... but there is a possibility that the Client will send it back to us for additional work, when that happens we go back to Situs_ID record and add another status InStChID = 11 which actually means "Redaction Request Received" then that's the time it should show up in the query again so that the user can work on it. then after the user finishes the additional work, the user will add another status InStChID = 16 which means "Redaction Sent to Client" then it shouldn't show up in the query anymore...

I hope it lessen up the confusion, and I would really appreciate your help...

Thank you

[NTC]

in query design view;
in the column; InStChID
in the 'Criteria' field put: <>10

run query

hope this helps.