Not exists element while also multiple join criteria

Question

I want to find all entries of a table which have another entry of a certain property and do not have another entry, not with this property. And to connect entries I need to look at two non-unique fields.

A simplified version of data is like this:

ID	ORDER_ID	ITEM_ID	UPLIFTED	STATUS	INTENDED_RESULT
1	EE000000	1		Backorder	Selected
2	EE000000	1	U	Shipped	Not selected
3	EE000000	2		backorder	Not selected, due to entry 4 present
4	EE000000	2	U	Delayed	Not selected
5	EE000000	2	U	Shipped	Not selected
6	E0000001	1		Backorder	Not selected
7	EE000002	1	A	Jetplane	Not selected

Where I only want entry ID=1 to be selcted. As it does not have "U" in UPLIFTED and where exists another entry (entry ID=2) with same ORDER_ID and ITEM_ID which have "U" in UPLIFTED and where not exists another entry with same ORDER_ID and ITEM_ID which does not have STATUS="Shipped" and "U" in UPLIFTED

NB: It must be somewhat efficient too, as the real data is a table with about 40000 entries and 35 columns.

I had expected it to be done with:

SELECT TEST_TABLE.*
FROM TEST_TABLE 
   INNER JOIN TEST_TABLE AS TEST_TABLE_1 
      ON TEST_TABLE.ITEM_ID = TEST_TABLE_1.ITEM_ID AND TEST_TABLE.ORDER_ID = TEST_TABLE_1.ORDER_ID
   WHERE (TEST_TABLE.UPLIFTED Is Null Or TEST_TABLE.UPLIFTED <> "U") 
      AND TEST_TABLE_1.UPLIFTED = "U" 
      AND NOT Exists (
            SELECT 1 
               FROM TEST_TABLE tt3 
               WHERE tt3.ORDER_ID = TEST_TABLE.ORDER_ID 
                  AND tt3.ITEM_ID = TEST_TABLE.ITEM_ID 
                  AND tt3.STATUS <>"Shipped"
         )

MS Access design view of expected query

However, this query, does not return any entries at all.

Answer 1

I don't know how efficient this is but it does return desired output from given sample data:

SELECT Test_Table.*
FROM Test_Table
WHERE ((([Order_ID] & [Item_ID]) 
    Not In (SELECT Order_ID & Item_ID FROM Test_Table GROUP BY Order_ID & Item_ID 
            HAVING Sum(IIf(Status<>"Shipped" AND Uplifted="U",1,0)) >=1) 
    And ([Order_ID] & [Item_ID]) 
    In (SELECT ORDER_ID & Item_ID FROM Test_Table GROUP BY Order_ID & Item_ID HAVING Count(*)>1)))
AND Nz(Uplifted,"")<>"U";