Avatar of dazzler77
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?
Microsoft Access

Avatar of undefined
Last Comment
Jim Dettman (EE MVE)

8/22/2022 - Mon
Jim Dettman (EE MVE)

What you want to do is break this down one step at a time.  Each table should represent one "thing" and all the fields in that table should relate to it.

<<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.
dazzler77

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.

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
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Your help has saved me hundreds of hours of internet surfing.
fblack61