dazzler77
asked on
MS Access Database Design Problem for test results
I am trying to build a database to store test results, and requirements tracking.
Each test has a step number and an instruction step.
My problem is there are different settings for each test which I would normally like to have different columns for.
For example the first 100 tests are like this:
test 5 degrees 10 degrees 20 degrees
1 pass fail pass
2 pass pass pass
For example the next 50 tests, each test has different setting, but does not have different temperature ranges.
If I were doing object programming, I would have a base class, with each test having a class with different properties.
In access, I could use a different table for each test result set, but I would like to print out the report as one document.
I also want to link specifications to each test step.
Also, if I have different tables, how do I make relationships to different tables from one table to another?
eg. I have a table of specifications, and some test steps are in different tables.
Specification Test step Result
1.2.3 1.4 pass
1.2.4 2.5 pass
Another way I thought was to use attached properties:
test property setting
1 temp 5
1 dial 3
1 voltage 7.4
test property setting
2 temp 10
2 dial 3
2 voltage 7.4
Using this method, it is very time consuming attaching each of these properties to each test, it would be easier to have these as columns in a table. Just that the columns are different for groups of tests.
Maybe I am going about this the wrong way. What is the best method to achieve this?
Each test has a step number and an instruction step.
My problem is there are different settings for each test which I would normally like to have different columns for.
For example the first 100 tests are like this:
test 5 degrees 10 degrees 20 degrees
1 pass fail pass
2 pass pass pass
For example the next 50 tests, each test has different setting, but does not have different temperature ranges.
If I were doing object programming, I would have a base class, with each test having a class with different properties.
In access, I could use a different table for each test result set, but I would like to print out the report as one document.
I also want to link specifications to each test step.
Also, if I have different tables, how do I make relationships to different tables from one table to another?
eg. I have a table of specifications, and some test steps are in different tables.
Specification Test step Result
1.2.3 1.4 pass
1.2.4 2.5 pass
Another way I thought was to use attached properties:
test property setting
1 temp 5
1 dial 3
1 voltage 7.4
test property setting
2 temp 10
2 dial 3
2 voltage 7.4
Using this method, it is very time consuming attaching each of these properties to each test, it would be easier to have these as columns in a table. Just that the columns are different for groups of tests.
Maybe I am going about this the wrong way. What is the best method to achieve this?
ASKER
thanks for your comments Jim,
You are correct that there are lots of different tests.
A specification is like:
1.1 For each temperature below 20 degrees, the output variation shall be within 5%.
1.2 For temperatures above 20 degrees, the output variation shall be within 10%
1.3 The output for each combination of voltage inputs will not change.
You an ignore the specifications as this is easy to relate once I have I have a table of all my tests.
A 'test' is just a combination of settings. one test might relate to specification 1.1 and 1.3 and another test might relate to specifications 1.2 and 1.3 These can be related in another table.
For each test or combination of settings, I need a result. The result is pass or fail, or it is a voltage or number output. Some tests I need to repeat many times. Others I may repeat a few times. Each time I would like to record the date and time.
So I would like to do something like this
Test 1 is related to specification 1.1 and 1.3. The settings for this test are: temperature = 25, dial = 3, voltage = 7.4. Measure the output. The output is to be within the range 5.5 and 6.2.
Test 1 attempt 1 = pass (record date and time of test)
Test 1 attempt 2 = fail (record date and time of test)
In the future I may add different temperatures, or voltages, or dial settings.
Yes it is just another test. But for each temperature I have to test each combination of other voltage and setting. I really would like a 3 dimensional matrix.
You are correct that there are lots of different tests.
A specification is like:
1.1 For each temperature below 20 degrees, the output variation shall be within 5%.
1.2 For temperatures above 20 degrees, the output variation shall be within 10%
1.3 The output for each combination of voltage inputs will not change.
You an ignore the specifications as this is easy to relate once I have I have a table of all my tests.
A 'test' is just a combination of settings. one test might relate to specification 1.1 and 1.3 and another test might relate to specifications 1.2 and 1.3 These can be related in another table.
For each test or combination of settings, I need a result. The result is pass or fail, or it is a voltage or number output. Some tests I need to repeat many times. Others I may repeat a few times. Each time I would like to record the date and time.
So I would like to do something like this
Test 1 is related to specification 1.1 and 1.3. The settings for this test are: temperature = 25, dial = 3, voltage = 7.4. Measure the output. The output is to be within the range 5.5 and 6.2.
Test 1 attempt 1 = pass (record date and time of test)
Test 1 attempt 2 = fail (record date and time of test)
In the future I may add different temperatures, or voltages, or dial settings.
Not sure where the degree changes fit in (5, 10, 20); would that not be conditions for a specific test? Or in other terms, it's really just another test?
Yes it is just another test. But for each temperature I have to test each combination of other voltage and setting. I really would like a 3 dimensional matrix.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<Using this method, it is very time consuming attaching each of these properties to each test, it would be easier to have these as columns in a table. Just that the columns are different for groups of tests.>>
The rub with that is that every time one of your test changes or a new test comes along, you database design would need to change. But if you do it right, the only thing that should need to be done is records added.
You've mentioned a couple of things in the above, and I'm not quite clear on how they all relate:
Specifications
Tests
Results
Not sure where the degree changes fit in (5, 10, 20); would that not be conditions for a specific test? Or in other terms, it's really just another test?
Probably what would be simplest is to walk us through a actual example of something you would test.
Jim.