Help with SQL for Access - Need to fetch record based on another record

[iggydarsa]

Hi,

I have a table that looks like this:

ClientNo | ClientName | Status | Date

AAA | Andrew | Start | 1/1/17

AAA | Andrew | Round1 | 2/2/17

AAA | Andrew | Done | 3/3/17

AAA | Andrew | Round2 | 4/4/17

AAA | Andrew | Done | 5/5/17

BBB | Bob | Start | 1/2/17

BBB | Bob | Round1| 2/3/17

CCC | Charlie | Start | 5/5/17

CCC | Charlie | Round2| 5/6/17

CCC | Charlie | Done | 7/7/17

DDD |Dawn |Start | 8/8/17

DDD | Dawn |Round1 | 9/9/17

DDD | Dawn | Done | 10/10/17

DDD | Dawn | Round2 | 11/11/17

DDD | Dawn | Done | 12/12/17

DDD | Dawn | Round2 | 12/13/17


What I'm trying to accomplish is to fetch all the ClientNo and ClientNames that has Round2 that has Done after it, as the last couple of actions.

For instance AAA - Andrew and CCC - Charlie would fall into this category but DDD - Dawn would NOT because the last "Round2" is not followed by "Done"

No matter what I tried did not satisfy the need.

Any help is appreciated.

-Iggy

 

[james_lankford]

get a list that are Done
then join that to those that have a Round2

select 
  ClientNo, 
  ClientName, 
  Date,  
from 
  my_table 
  inner join 
  (
    select 
      ClientNo, 
      ClientName, 
      Date,  
    from 
      my_table 
    where 
    (
      Status = 'Done'
    ) as tbl 
  )
  on 
  my_table.ClientNo = tbl.ClientNo 
  and 
  my_table.ClientName = tbl.ClientName
where 
(
  Status = 'Round2'
  and 
  my_table.Date <= tbl.Date
)

or do it the other way
get a list that have a Round2 and then join it to those that are Done