How to Compare values between rows in access query

Question

I have a table table1 like below. I want to find all H in Status field that is in between two L. The output for mentioned criteria should be 04/01/15, 05/01/15 and 07/01/15. How can I solve this?

Date          Status
01/01/15        A
02/01/15        H
03/01/15        L
04/01/15        H
05/01/15        H
06/01/15        L
07/01/15        H
08/01/15        L

Answer 1

I think you can use a query like this:

SELECT 
    t.Date, t.Status
FROM 
    yourTable AS t
JOIN (
    SELECT MIN(Date) AS minDate, Max(Date) AS maxDate
    FROM yourTable
    WHERE Status = 'L') AS l
ON t.Date >= l.minDate
    AND t.Date <= l.maxDate
    AND t.Status <> 'L';