Table design problem

[Dweeber]

I think I have a many-to-many relationship problem, but studying
examples of how to implement a many-to-many relationship hasn't helped
me solved my problem.

I have Samples. The Samples have various attributes. All attributes
have "target", "upper limit", and "lower limit" values. (The number of
attributes each sample has varies.)

"Copies" of the "Samples" are then tested. For each "Copy", I need to
record test values for each attribute, and be able to compare the test
value against the Sample attribute values- which requires matching the
attribute type of the test record with the attribute type of the
sample record.

Any help would be greatly appreciated!

 

[Tom van Stiphout]

On Fri, 03 Jul 2009 14:28:18 -0400, (e-mail address removed) wrote:

tblAttributes
AttributeID PK autonumber
AttributeName text

tblSamples
SampleID PK autonumber
SampleName

tblSampleValues
SampleID PK long
AttributeID PK long
TheValue double? required

tblCopies
CopyID PK autonumber
SampleID FK long
CopyName

tblCopyValues
SampleID PK long
AttributeID PK long
TheValue double? required

So you have two M:M relations. Comparing data should be simple via
tblCopies.SampleID.

-Tom.
Microsoft Access MVP

 

[Dweeber]

Thanks for your help. I kept getting close but something always was
wrong. I notice right away in your layout a symmetry that I thought
should be there, but never achieved in my attempts.

Since the Attribute Values for the Samples are actually three
different values ("target", "lower", "upper") I should adjust the
tblSampleValues to this:

tblSampleValues
SampleID PK long
AttributeID PK long
tblSampleTarget dbl
tblSampleUpper dbl
tblSampleLower dbl

Let me know if you disagree.
Regards,
David G.

 

[Tom van Stiphout]

On Fri, 03 Jul 2009 18:57:35 -0400, (e-mail address removed) wrote:

That may be acceptable if you promise you will keep it at three

-Tom.
Microsoft Access MVP