Self Join with dynamic clause / criteria in Access VBA

ID   Date           Estimate   RowNo    PreviousARowno

33   30/06/2014       A        1
33   01/07/2014       A        2
33   02/07/2014       A        3
33   03/07/2014       E        4
33   04/07/2014       E        5
66   30/06/2014       A        6            
66   01/07/2014       E        7            
66   02/07/2014       E        8            
66   03/07/2014       E        9            
66   04/07/2014       E        10

In this dummy table I would like to Update the PreviousARowno column with the following logic: the PreviousARowno value should be the Row Number of the previous most recent Date where Estimate is A, so it shoud look like this:

ID   Date           Estimate   RowNo    PreviousARowno

33   30/06/2014       A        1            
33   01/07/2014       A        2            1
33   02/07/2014       A        3            2
33   03/07/2014       E        4            3
33   04/07/2014       E        5            3

66   30/06/2014       A        6            
66   01/07/2014       E        7            6
66   02/07/2014       E        8            6
66   03/07/2014       E        9            6
66   04/07/2014       E        10           6

Any ideas or suggestions on how I could update that column? I’ve tried different self joins with select top 1 statements but I cant make it work dynamically. If the date for the previous A is a known-prefixed value then I know how to do it but unfortunately is unknown. By the way I’ve added the RowNo column for that reason so it can be used as a criteria for the joins.

Answer

You can do this with a correlated subquery:

select d.*,
       (select max(d2.rowno)
        from dummy as d2
        where d2.id = d.id and d2.rowno < d.rowno and d2.estimate = 'A'
       ) as PreviousArNo
from dummy as d;

Advertisement

Answer