I have a database that logs users' test results for ESD equipment they wear on their body. All users have a wrist strap they must test. Many have a heel strap on each foot they must test, (but not all).
If a user tests their equipment, a record will be inserted with all PASS/FAIL information.
I will see:
WRIST_STATUS: Pass, NotUsed, Fail
LFOOT_STATUS: Pass, NotUsed, Fail
RFOOT_STATUS: Pass, NotUsed, Fail
Here is my query that is used to view the data:
SELECT FIRST_NAME, LAST_NAME, DATE_TIME, WRIST_STATUS, WRIST_RESISTANCE, LFOOT_STATUS, LFOOT_RESISTANCE, RFOOT_STATUS, RFOOT_RESISTANCE FROM ESD_RESULTS ORDER BY ENTRY_NUMBER ASC;
This query currently displays this data:
I'd like to refine this query. If any one status = "Fail", then I highlight the cell in red; else I highlight in green. This is very visual.
As this list grows throughout the course of the day, it will be hard to determine who had any "Fails" but did not go pack and re-test until they got "Pass" results.
How can I write a query that shows me all the people who had a "Fail" in any one of the status fields, but do NOT have a record later in the day with all "Pass" or some combination of "Pass" and "NotUsed" ("NotUsed" is used by folks who do not need to test their heel straps.