Your pictures are what I am trying to get at. After I posted my question I did come up with a solution that seems to work but it may be a bit cumbersome.
Table 2 inspections - What I did in my inspection table (Table 2) is created a formula field (Max date failed inspection) that looks at the pass/fail field for that inspection and if it “failed” it records the date of the failed inspection. That gives me a field I can sort for MAX date on from the company info table (Table 1).
Table 1 Company info - In Table 1 I already had a roll-up field that captured the MAX Inspection date for the company (most recent inspection) from Table 2. I then created another Roll-up field in Table 1 to sort MAX date on failed inspections from Table 2. Still another field I created was a formula field in Table 1 to compare the Max inspection date field (table 1) against the Max Failed inspection date field (table 1) and if there is a match in the dates it enters a “Failed” value and if it doesn’t match it leaves the field blank.
Not sure if there is an easier way or more direct way of doing it but that seems to work. Still testing it though.
How where you proposing to accomplish the results in your picture?