Filter for records where Field = True, but also the record before that.

[MatthewGrace]

Shown below is a very simple table. I want to write a Query that does two things:


1) Filter by "Condition Met" = True ... no problem.
2) Also shows the record before! I have no idea how to do this.

For clarity, applying those rules to the table below would yield records with ID#'s 3, 6, and 10 because of the checkmarked/true condition, and also somehow return records immediately preceeding them with ID#'s 2, 5, and 9 in the same result set.

Thank you for any input.

[NTC]

when Query1 is in design view; in the 'Criteria' row under the ConditionMet field you put: -1

[MatthewGrace]

For the first goal of the query, that's correct, that's the easy part. But the second part of the query seems impossibly difficult: include the previous record as well. I'm guessing some sort of nested query, or possibly even vba programming? no idea.

[NTC]

the correct implementation depends upon the data integrity. If the next record preceding it is always a prior date or a prior ID then:

Using ID field as example:

join Query1(described previously) to the table joining the 2 ID fields

then you must put the query into SQL View and manually modify the join syntax from 'ID=ID' to be 'ID=ID AND Table.ID = Query1.ID-1'
this isn't the exact syntax as you must include the table name but hopefully you get the jist
you won't be able to view this query in design view once you do that
this is advanced

[MatthewGrace]

Wow, I can't believe it actually worked lol. Yes it took a little bit of playing but I got it (as it happens the "AND" you mentioned should be "OR"), and it felt good to see it accomplished. Thank you so much, Matt.