VBA ACCESS: Select/Filter exact previous&next rows based on specific value-CodePudding

I'm trying to optimize the filtering of data in one report/table and I've encountered a challenge. Table is located in m.access, so any vba access code or sql query should work here.

So far I've tried few options, but could not achieve expected results:

select prev_type, type, next_type from ( select *, lag(type) over (order by id) as prev_type, type, lead(type) over (order by id) as next_type from table ) as t where type = "type";

Basically I want to display from below table three rows:

row with Type = 'D'
previous row to the one with Type 'D'
next row to the one with Type 'D'

enter image description here

CodePudding user response：

Try with a subquery:

Select * From YourTable 
Where Abs([ID] - (Select ID From YourTable Where [Type] = 'D')) <= 1

For multiple Ds, join the subquery:

Select 
    * 
From 
    YourTable ,
    (Select ID From YourTable Where [Type] = 'D') As T
Where 
    Abs(YourTable.[ID] - T.ID) <= 1