MS Access Iterator ? 1

[jibberski]

I have an application that stores data using iterators (as below)

ID Description Level
600 Demolition -1
670 Demo Exterior Flat Work 0
675 Remove Foundation Walls 0
680 Remove Concrete Stoops/Steps 0
900 Concrete Curb Cutting & Coring -1
910 Core Through Existining 0

I want to retreive the data from 0600 upto the next record with a "Level = -1."

SELECT ID, Description, Level
FROM Table
WHERE ID > 600 but < Next Record with "Level= -1"

Thanks for your time!

 

[DanJR]

if ID is numeric and as order then:

one way is ....

SELECT *
  FROM myTable AS t
  WHERE ID >= 600 
    AND (ID < (SELECT min(ID) 
                 FROM myTable t2 
                 WHERE t2.ID > 600 and Level = -1)
    OR NOT EXISTS  (SELECT * 
                      FROM myTable t3 
                      WHERE t3.ID > 600 and Level = -1));

the "NOT EXISTS" just takes into account when there is no subsequent ID with a level = -1 e.g. if you were to replace "600" with "900