I am just in the planning point on this.

Scenario - We take a sample from a client and test it for several conditions. (Call them alpha, beta, gamma, delta, elipson)
If in 4 consecutive samples, 2 of the 4 fail FOR THE SAME TEST, then a warning needs to be generated and sent to the client.

Sample 1 - Alpha fails, rest pass


Sample 2 - Beta fails, rest pass
Sample 3 - Gamma fails, rest pass
Sample 4 - Delta fails, rest pass
This would be passing because elements failed no more than one time in the series. No notice would be needed.

Sample 1 - Alpha fails, rest pass
Sample 2 - Beta fails, rest pass
Sample 3 - Alpha fails, rest pass
Sample 4 - Delta fails, rest pass
This would be failing because Alpha failed twice within the series. A notice would be issued.

How do I design things (tables & queries) to pull this off. I am thinking of the following tables:
Clients
AllowableTestResults
Tests
(Other tables for unrelated purposes)

Should I run seperate queries for each test and then look for 2 of 4 fails individually or run one query which does everything at once?

Thank you