Query Criteria Help 1

[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

 

[iuianj07]

I tried to change the query

qryLoggedIndexingIDs from:

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();

to:

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 Or T3.InStChID = 16) Or (T1.Situs_ID) In (SELECT DISTINCT T3.Situs_ID FROM Indexing_Loan_Status As T3 WHERE T3.InStChID = 11)) AND ((T2.Analyst)=getuserName()));

and what this does now, if a Situs_ID has an InStChID = 11, it will show up in a query, but if it doesn't have InStChID = 11 but there is InStChID = 10, it now doesn't show up in the query... which is correct...

Although, If there is InStChID = 11, and I added another record with InStChID = 16, it still shows up in the query, but what I want it to do is if there's InStChID = 16, then it shouldn't show up anymore...

Any help is greatly appreciated...

thank you

 

[PHV]

What about this ?

SELECT T1.Situs_ID, T2.Analyst, T2.InStChID
FROM Job_Tracking AS T1 INNER JOIN Indexing_Loan_Status AS T2 ON T1.Situs_ID = T2.Situs_ID
WHERE T1.Situs_ID Not In (SELECT Situs_ID FROM Indexing_Loan_Status GROUP BY Situs_ID HAVING Max(InStChID) IN (10,16)) 
AND T2.Analyst=getuserName()

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

 

[iuianj07]

Thanks PHV,

I tried to do a work around and changed it to and named it qryLoggedIndexingIDs2:

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 Or T3.InStChID = 16)) AND ((T2.Analyst)=getuserName())) OR (((T1.Situs_ID) In (SELECT DISTINCT T3.Situs_ID FROM Indexing_Loan_Status As T3 WHERE T3.InStChID = 11)) AND ((T2.Analyst)=getuserName()));

then I created another query:

SELECT qryLoggedIndexingIDs2.Situs_ID, qryLoggedIndexingIDs2.Analyst, qryLoggedIndexingIDs2.InStChID
FROM qryLoggedIndexingIDs2
WHERE (((qryLoggedIndexingIDs2.Situs_ID) Not In (SELECT DISTINCT T3.Situs_ID FROM Indexing_Loan_Status As T3 WHERE T3.InStChID = 16)));

it gives the same result as what you have posted, will this have the same result everytime? And it's just that I took the long (probably wrong) approach in achieving the desired result?

Thank you,

 

[PHV]

it gives the same result as what you have posted
Wrong or good result ?
If good, why overcomplicate the processus ?

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

 

[iuianj07]

if good, why overcomplicate the processus"
Haha because I am dumb enough to think of the simpler way to write the query and had to write 2 queries instead... Thanks!