I think that this is more of a relationship question than a formula question. You have a relationship where one water system has many water samples.
Do you want to fire a notification where the second most recent water sample was a fail but the most recent sample notification was a pass. I will make the assumption that they sample at most once per day.
On the Relationship, make a Summary Maximum of the date of the sample where the pass/fail is equal to fail. Call it [Most Recent Failure Date]. Lookup that field down to the detailed samples children table.
Make another summary field of the minimum of the sample date where the pass fail is equal to pass and also subject to the condition that the sample date is greater than [Most Recent Failure Date]. Call this [Date of 1st Pass after a Fail]
Then simply set up a notification to Fire when the pass/fail is equal to pass but the [Sample date] equals the date in the field [Date of 1st Pass after a Fail]
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------