Software

Ask something
Discussion Locked

Data Base Design Question

0 helpful
3 replies

by pdouglas4294

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

3 replies

by iht4

Hi,

I will make a presumption in that every time you run tests you run all 5 tests. If this is the case my recommendation would be:

Table 1: Product table
Product No | Product Name | (Other product details as required)

Table 2: Test Table
Product No | Test Date | Test 1 | Test 2 | Test3 | etc

This should normalise to a fair degree!

As regards the query, I will have a think about that!

hth
Iain

by pdouglas4294

Poster rated this answer.
I've given a lot of thought on the table design and that is how I was planning on doing the tables.
The hard part is the query.

by iht4

For the SQL query try:
add a unique ID (UID)to test table:

Select Count(UID)
FROM [test table]
WHERE Id in (SELECT TOP 4 UID
FROM [test table]
WHERE [product ID] = 'a product'
ORDER BY Test Date DESCENDING )
AND Test1 = 'fail'

will need to be iterated for each test and each product.

hope this might provide a starting point?

cheers
Iain

This discussion is closed to new replies.