One to one relationship table design problem

[swaybright]

I am trying to set up a correct normalized table structure that involves what I supposed should be a series of one to one relationships, but as I am thinking through the outputs of the database, I am not so sure. The basic problem is as follows:
I have a table of samples upon which I may or may not perform several tests. Each test has it's own output fields. Additionally each test has a set of conditions--sometimes the same as another test and sometimes different.
I would like to output the results of the tests run on each sample along with the conditions with which the test was run.

My current structure has a data table for each test with fields for each result of that test, it also has a foreign key to a conditions table. I have a one-to-one relationship between each test table and the sample table.
I would like my final query to arrange the data by sample, conditions, then test result for any test run--if a test was not conducted a null field would result. I am having difficulties in setting up this query, so I am suspicious that my data structure is flawed.

Any thoughts?
Shane

 

[LaurieHamlin]

Why do you have a different table for each test, instead of one table that includes a Test Name field?

 

[swaybright]

I chose to make a new table for each test because each test has different measurements. So one test will measure say length and weight, while another will measure height and still another may measure softness and stretchiness. Each test has different fields to put values in, so if I were to create a single table for all tests, many fields would be blank because I do not necessarily run all tests on all samples.
I am oversimplifying the situation a bit, but any one test may have from 5 to 15 different things measured and I could perform any number of 8 different tests on a sample

Shane

 

[dhookom]

I would seriously consider normalizing your tables so that the result of each measurement creates its own record. If a sample has 8 different tests, that would create 8 records.

Duane
Hook'D on Access
MS Access MVP

 

[swaybright]

Laurie,

Thanks for the question, because I have thought more about the point you are making. Having stepped back from the problem, I think rather than having the awkward 1 to 1 tables, I should instead place the a Sample foreign key into the conditions table and create 3 new tables:
TestList -- a lookup table with a list of tests
MeasureList -- a lookup table with a list of measurements (maybe linked back to test list to select only the measures relevant for a selected test
Test -- with foreign keys linking the conditions table, testlist and measurelist, that holds the value for each measurement.

now for a query, I should be able to create a crosstab query that tabulates each measure value for each sample and condition.

 

[swaybright]

Ok, so I have a table for measurement records as Duane suggests. I am running into an issue in that some measurement values are text and others are numbers. Any suggestions as to how to keep text out of number fields?

Thanks!

 

[dhookom]

I would add a field to the TestList table that contains data type. You can then use code in your input forms to validate the entries.

Duane
Hook'D on Access
MS Access MVP