CODEWITHSUNDEEP
sqlms-accessms-access-2016
Elliott Addi
Elliott Addi

Reputation: 390

Querying a Table with two ID Fields

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.

Upvotes: 0

Views: 44

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

Another method uses aggregation:

select ref
from actions
where status in ('new', 'done')
group by ref
having min(status) = max(status) and min(status) = 'new';

Upvotes: 1

Lee Mac
Lee Mac

Reputation: 16015

You could LEFT JOIN your Actions table to a nested SELECT query retrieving all references with status Done, with a test for records with no corresponding record in the nested query, e.g.:

SELECT a.*
FROM 
    Actions a LEFT JOIN (SELECT b.Ref FROM Actions b WHERE b.Status = 'Done') t
    ON a.Ref = t.Ref
WHERE
    a.Status = 'New' AND t.Ref IS NULL

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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

Upvotes: 3

Related Questions