Desktop

Ask something
Discussion Locked

Data Base Design Question

0 helpful
4 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.

4 replies

by jimhm

What is your projection of table growth? What is the initial size of the table? What kind of system will this be run on? Will it be Server based or individual work station? Is this a single use Database (not to go state wide)?

From those questions are you sure that Access is your product of choice - what do you have Oracle licensing or Teredata or and db system that will handle the data volume - and access volume.

Then we can more closely look at the design.. Have you normalized the data, yet?

by zcool

The first question you should look at is how large you might expect the DB to grow. If you expect thousands and thousands of records and you expect to be querying this data daily, then you might want to look at using a real server based DB like MSSQL or MySQL. That being said, I would most certainly suggest one table per test. This will allow for great speed once the total number of records gets large. The key to your solution though is the query. This would have to be a very complex series of queries (it will run faster with more smaller queries instead of one real big one). Perhaps have one query pull out all records where the test failed for each test. Store the results in a table (I would use yes or no selections, this will actually be a 1 or a 0 in the table). Then a second query would check to see if the same test failed twice for any particular product or run. Good luck.

by dwdino

What you need to do is run a select query, then count to the records.

(psuedocode)
dim array (alpha, beta, gamma, delta, elipson)
dim recordset
open recordset
for each code in array
select item1 from table where failurecode = code
count recordset
if count >= 2 then MSG item1 1 has code failure
next
close recordset

by iabelyan

hi, i would like to get info. on LAN. and what items software should be acqure and applications will the software be used for.
also what additional pieces of hardware must be purchase and attach to the cpu?
and last question. What additional personnel, if any, would be requred when the coputer becomes operational? what will be done with edisting personnel? Please email me the info. Thank you!!!

This discussion is closed to new replies.