I am looking at setting up a data base for storing analytical results of dairy products.
(Access 2000 with VBA for good measure)
Each sample is evaluated with about 8 different tests and different Pass/Fail thresholds for different products (raw milk vs pasteurized, …)
I have one table with the allowable thresholds to compare to for each of the different products.
Where I am needing help is in the following part:
We have what is called a “2 out of 4” where if a commodity fails twice in the last 4 times for the same test, a notice is generated.
Condensed sample. We test for Alpha, Beta, Gamma, Delta, and Elipson.
First sample passes on all but Alpha
Second sample passes on all but Beta amd Delta
(Allowable – not the same test failing twice)
Third sample passes on all but Gamma
(Allowable – not the same test failing twice)
Fourth sample passes on all but Alpha.
At this point, in running this query, there should be notification of a problem.
I am looking for suggestions to design this “can of worms”. One table for all of the results vs. one table for each test type. Suggestions on the Query.