Multiple one-to-many relationships in the same table

[Martino165]

I was wandering the best way to handle setting up relationships and/or
table structure when have have a table in a one-to-many relationship
with multiple fields in another table

e.g.

Table Results
Study_Results1
Study_Results_Unit_ID1
Study_Results2
Study_Results_Unit_ID2

Table Units (did not use lookup wizard, but will use this table in
forms as lookup values)
Study_Results_Unit_ID
Unit_Short_Name
Unit_Long_Name

As you can see the 'Study_Results_Unit_ID' would have a relationship
with both 'Unit' fields in the Results Table. The Units will be
difference for Result1 and Result2.

Any advice. I was trying to create one master list of units that I
could use accross the database to populate any type of units field
(i.e. time, weight,results,etc.)

Thanks for any help

 

[Steve Schapel]

Martino,

It would probably be better to change the structure of the Results
table. In fact, much better. Just one field for Study_Results_Unit_ID
and another field for the designation e.g. 1 or 2 in your example.
Therefore, the 2 study results would be in 2 separate records, rather
than in 2 separate fields in a single record.

If that doesn't help, please post back with some more details of your
data, with some examples.

 

[Martino165]

Thanks for the reply..

some more info is needed though..

Result1 and Result2 and the same result essentially, just conversions
of each other...
e.g. 1000 ppm = 50 mg/kg/day

Therefore, I need and want the two results to be together in the same
record...

Another example would be if a single table had Animal Information. It
could have age and weight, both of which require Units 6-month and 325
grams. I am trying to use a single table of units to pull from.
Otherwise I could have to create 50 different unit table for each field
that requires units. I hope this better explains it.

From the reading I have done, it is no problem in setting up the

relationships. Please let me know if I am wrong is assumming that.
The other thing is that if it is allowed will I run into problems down
the road when trying to query the data or performs updates etc....

thanks,
matt

 

[Steve Schapel]

Matt,

... I need and want the two results to be together in the same
record...

Well, to be frank, what you want comes secondary to relational data
principles. And what you think you need is not always so. I will take
your word for it, as I don't know enough about your project to be
definitive. But 50 fields requiring units makes me strongly suspect
that you are falling into the "fields as data" trap.

... it is no problem in setting up the
relationships.

There is only one real reason to define Relationships, and that is to
enforce Referential Integrity. In the example you have given, this
would mean making sure that a Unit_ID is not entered into the main table
if it doesn't exist in the Units table. The design of your form and
data entry process will almost certainly take care of this anyway, so
setting up Relationships between the tables is a trivial exercise, and I
certainly wouldn't bother with it myself.

 

[Larry Linson]

Result1 and Result2 and the same result essentially,
just conversions of each other...
e.g. 1000 ppm = 50 mg/kg/day

Therefore, I need and want the two results to be
together in the same record...

Is the information available so that one could be calculated from the other
when needed? If so, that is the recommended approach -- storing redundant
data violates relational DB design principles.

And, from your description, you feel there is a need to violate relational
design principles.

I suggest you may not _need_ both values -- that it is very likely you can
calculate one from the other when needed.

Perhaps, in your view of what you are doing, these are items that are so
commonly used that you want to calculate them once and use the
already-calculated values when you need them; but, as is likely obvious from
your questions and the responses, that view is causing you problems, and is
causing us problems in even understanding what it is you have and what you
need to do with it.

Larry Linson
Microsoft Access MVP

 

[Martino165]

The multiple results are conversions of each other, but would not be
able to be calculated. They are values pulled directly from study
reviews (e.g. Decreases body weight gain at 500 ppm (32 mg/kg/day))
The background calculation is based on individual animal data and this
data is not available. Overall I think I have gotten away from my
initial inquiry.

What I am really trying to get at is whether or not I can use a single
table of Units as a 'lookup table', and if so, should relationships be
built?

Inherently tables could have multiple fields that could pull from this
list of units. a Time field, Weight Field, Age Field, Results Field
etc...

as of now I have a single table of units with the fields named much
like:
Units_ID
Units_Short_Name
Units_Long_Name


All of the fields in the other tables that require some sort of unit is
named something like
Animal_Weight_Unit_ID
or.. Animal_Age_Unit_ID

I will only reference the units table in the combo boxes in the input
forms (no lookup wizard or anything)

Should I create a one-to-many relationship at every Units_ID field. If
I do or don't what are the plusses and minuses

 

[Steve Schapel]

Martino,

.... Overall I think I have gotten away from my
initial inquiry.

Apologies for lack of clarity. I thought I had answered these questions...

What I am really trying to get at is whether or not I can use a single
table of Units as a 'lookup table'
Yes.

and if so, should relationships be
built?
No.

Inherently tables could have multiple fields that could pull from this
list of units. a Time field, Weight Field, Age Field, Results Field
etc...

As I mentioned before, I would probably not do it like this. I would
consider normalising this data into a single field in a related table.

as of now I have a single table of units with the fields named much
like:
Units_ID
Units_Short_Name
Units_Long_Name

This is ok. However, I would not do it like this myself. Will there
ever be 2 items in this table with the same Units_Short_Name? The only
reason to use a Units_ID field is to guarantee uniqueness, and as this
is a lookup table, all the rercords will be unique anyway, so I would
not have a Units_ID field in this table. I would use the
Units_Short_Name as the field for linking to the units field(s) in the
other table(s).

All of the fields in the other tables that require some sort of unit is
named something like
Animal_Weight_Unit_ID
or.. Animal_Age_Unit_ID

I will only reference the units table in the combo boxes in the input
forms (no lookup wizard or anything)

Should I create a one-to-many relationship at every Units_ID field.
No.

If
I do or don't what are the plusses and minuses

If you don't, the only minus would be that you could theoretically enter
a Unit into one of the units fields in the main table where that unit is
not included in the Units table. As I mentioned before, this is a
trivial reason, because you can easily enforce this rule in other ways
anyway, for example by setting the Limit To List property of your
comboboxes to Yes.

The other minus with the present approach is that you could still enter
an inappropriate unit. For example, you could enter 'kg' in the
Animal_Age_Unit field. There would be 2 ways of controlling this. If
you rationalised the data, as I suggested above, you could add another
field to the Units table to identify which results type the units are
applicable to, and then fileter the comboboxes' row sources accordingly.
Otherwise, you could scrap the Units table altogether, set the Row
Source Type property of the comboboxes to 'Value List', and enter the
applicable units for each field into the Row Source property of the
combobox. I would imagine that for at least some of these results,
there will only ever be one units value applicable anyway?