Querying a Table with two ID Fields

Question

I have a table called Actions with 2 identifiers: Ref & Status
Status can be any one of the following: new, done

  Ref  |  Status  |  Date   
  ---------------------------------- 
   1   |   new    | 10/31/2018   
   1   |   done   | 10/31/2018
   2   |   new    | 10/31/2018

I'd like to query only the actions that are new but not done. In this example, the query would only return the 3rd row of the table.

Here's what I tried so far, but I only get the action that have been put to status done:

SELECT [2].[project title], [2].ref, [2].[Date] As [Creation Date]
    FROM (SELECT * From T_ACTIONS Where status = "New") AS [1],  
         (SELECT * From T_ACTIONS Where status = "Done") AS [2]
    WHERE [1].[Project Title] = [Insert a valid : Project Title]
    AND [1].[REF] = [2].[Ref]

I'm using Access 2016.

Answer 1

Use NOT EXISTS :

SELECT a.*
FROM Actions a
WHERE Status = 'new' AND
      NOT EXISTS (SELECT 1 FROM Actions a1 WHERE a1.REF = a.REF AND a1.Status = 'Done');