Solved Combination Criteria (1 Viewer)

[samthomasny]

Can someone please help me on this? In the below screenshot I want the sql qry to return only 'Ord3'
Ord1 must be excluded because of 'FC' (easy <> "FC")
Ord2 must be excluded because one of those records have 'FC' (struggling)
Ord3 must be included since it doesnt have any 'FC' records

Right now "AP" Or "PO" And Not like "FC*" is returning me both Ord2 & Ord3

 

[pbaldy]

My first thought is 2 queries. The first finds all orders with a value of FC. Then use the unmatched query wizard with the original data compared to that query to find all those that don't have an FC.

 

[June7]

SELECT * FROM table WHERE NOT Ords IN (SELECT Ords FROM table WHERE Holds = 'FC')

 

[June7]

WHERE (((GTORHD.HORSTS)<>"C") AND ((GTORDT3.DSTATS)<>"C")) AND NOT GTSUSH.[UORD#] IN (SELECT [UORD#] FROM GTSUSH WHERE USUSCD='FC')

 

[samthomasny]

WHERE (((GTORHD.HORSTS)<>"C") AND ((GTORDT3.DSTATS)<>"C")) AND NOT GTSUSH.[UORD#] IN (SELECT [UORD#] FROM GTSUSH WHERE USUSCD='FC')

Worked perfect. Learned something new today. Thanks a lot.