One to one relationship table design problem
One to one relationship table design problem
(OP)
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
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
RE: One to one relationship table design problem
RE: One to one relationship table design problem
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
RE: One to one relationship table design problem
Duane
Hook'D on Access
MS Access MVP
RE: One to one relationship table design problem
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.
RE: One to one relationship table design problem
Thanks!
RE: One to one relationship table design problem
Duane
Hook'D on Access
MS Access MVP