I am
TOTALLY lost!
In Post #19 you show what you want to happen - there are 7 examples.
Now you change and say
But in the examples 3, 4 & 5, you show Fail Email
even when the last Status is "Pass"!!!
Attachment 29515
Do you want to use/check only the last 3 or 10 (if there are that many inspections)??
But again, your table "Inspection" has no field to sort by other than the autonumber field, which is an extremely poor choice. You need a Test or Inspection date/time, an Inspection number (like an invoice number),... something!
The
purpose of an AutoNumber field is to provide a
unique value that serves no other purpose than to make each record unique. It is only guaranteed to unique, NOT sequential, nor increasing positive (meaning it can go negative).