×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

One to one relationship table design problem

thread700-1749053

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

RE: One to one relationship table design problem

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

RE: One to one relationship table design problem

(OP)
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

RE: One to one relationship table design problem

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

RE: One to one relationship table design problem

(OP)
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.


RE: One to one relationship table design problem

(OP)
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!

RE: One to one relationship table design problem

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close