Form to Generate Data Matrix of Records

[bymarce]

My database contains scientific/experiemental data. I have a tables for
sample information, data results, conditions under which the experiment was
run (ie temperature = 40F), and others. Currently I haved a many to many
relationship between data results and conditions through a join table. I was
thinking about making it a one to many relationship. Any comments on this?
I thought about making this change b/c even though data records may have been
run under the same conditions they shouldn't be updated at the same time.
Also I'm trying to make a form that will generate a group of records to
represent a matrix of data. For example we want to run 3 samples with the
same experimental method under 3 different sets of conditions. For that
situation we need 9 data records and have to type them individually. I made
a form with a multiselect list box for the sample IDs and 4 subforms into
which conditions can be typed. Each subform will contain a unique set of
conditions. I think I'll need several append queries to do this but I'm not
sure where to start and if I should change my table set up first. Thanks for
the help.
Marcie

 

[Jeff Boyce]

I don't have a clear enough picture of your situation to recommend table
relationships.

It would probably help to see a bit more of an example and an outline of the
table structure. And you know your 'domain' better than we do.

Here's an example (grossly oversimplified) of a data structure for a
"registration" database:

tblStudent
StudentID
FName
LName
DOB
... (other student-specific data)

tblClass
ClassID
ClassTitle
ClassDescription
... (other class-specific data)

trelRegistration
RegistrationID
StudentID
ClassID
RegistrationDate
... (other 'registration'-specific data)

Since one student can sign up for many classes, and one class can have many
students signed up for it, these two are related many-to-many. To resolve
this, the third table is needed to show valid pairs.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[bymarce]

Thanks. Here's my tables.
MLOBOOK (this is the sample information table)
CatalogPrefix
CatalogYear
CatalogID (these three are a compound primary Key)
...

Data (This table holds results from scientific experiments.)
ID (PK)
CatalogPrefix
CatalogYear
CatalogID
Property
Method
ResultsNumeric
ResultsText
Units
...

Conditions (This table holds info about conditions used to run an experiment.)
ID (PK)
ConditionName
Value
Units

ConditionsDataJunction
ConditionsID
DataID

There is a one to many relationship from MLOBOOK to Data (lots of
experiments can be run on one sample). Each experiment (one data record) can
be run under multiple conditions (temperature=40C and pressure=1ATM...).
Multiple experiments can be run with the same conditions (Density and Boilng
Point can both be run at 1ATM but if I changed the condition for one
experiment I wouldn't want that condition to be updated for other
experiments). I have Data and Conditions set up as a many to many
relationship through the ConditionsDataJunction table but I'm wondering if it
would be better as a 1 to many. Should I add the DataID to the Condtions
table, eliminate ConditionsDataJunction, and make it a 1 to many? If I do
this I know I'll have lots of conditions records for Temperature, 40, C with
different DataID. Is this a problem? Thanks. I hope this is more clear.
Marcie

 

[Jeff Boyce]

I'm sorry, maybe I'm low on caffeine still this morning...

I'm not seeing the situation clearly enough.

What I did notice, though, is what looks like unnecessary duplication. Why
do you have [CatalogPrefix], [CatalogYear], and [CatalogID] all duplicated
in your table named [Data] (and by the way, that word may be reserved by
Access -- try renaming that table to something more meaningful and not
reserved).

I'm also a bit concerned about the [Conditions] table. That looks like you
might be using it to hold temperature conditions, and pressure conditions,
and ... If true, you would be using the same field to hold different
(*types of*) data, not a standard approach to data structure/design.

It sounds like you are saying that [MLOBOOK] stores sample info, but that
your [Data] (hate that name!) table stores test results. ... and your
ConditionsData table associates test results with conditions.

That all seems reasonable (if I've interpreted correctly).

So, what issues/problems are you facing with using this data structure (HATE
that table name!)<g>.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[bymarce]

Thanks for your comments. I think you've got it. Sorry about "data"
bothering you. In scientific research all results are called data. I'll
call it tblResults for the rest of this conversation. From your comments,
considering update anomilies, and speaking with another scientist here I
think I'm going to keep the relationship as is between tblResults and
tblConditions. There are cases in which multiple results should be linked to
the same conditions records (ie one experiment produces multiple resutls and
conditions should be updated together) and there are case in which multiple
results are run at the same condition but should have different condition
record (ie lots of unrelated experiments are run at 40C and should not be
updated together).
Marcie

I'm sorry, maybe I'm low on caffeine still this morning...

I'm not seeing the situation clearly enough.

What I did notice, though, is what looks like unnecessary duplication. Why
do you have [CatalogPrefix], [CatalogYear], and [CatalogID] all duplicated
in your table named [Data] (and by the way, that word may be reserved by
Access -- try renaming that table to something more meaningful and not
reserved).

I'm also a bit concerned about the [Conditions] table. That looks like you
might be using it to hold temperature conditions, and pressure conditions,
and ... If true, you would be using the same field to hold different
(*types of*) data, not a standard approach to data structure/design.

It sounds like you are saying that [MLOBOOK] stores sample info, but that
your [Data] (hate that name!) table stores test results. ... and your
ConditionsData table associates test results with conditions.

That all seems reasonable (if I've interpreted correctly).

So, what issues/problems are you facing with using this data structure (HATE
that table name!)<g>.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Thanks. Here's my tables.
MLOBOOK (this is the sample information table)
CatalogPrefix
CatalogYear
CatalogID (these three are a compound primary Key)
...

Data (This table holds results from scientific experiments.)
ID (PK)
CatalogPrefix
CatalogYear
CatalogID
Property
Method
ResultsNumeric
ResultsText
Units
...

Conditions (This table holds info about conditions used to run an
experiment.)
ID (PK)
ConditionName
Value
Units

ConditionsDataJunction
ConditionsID
DataID

There is a one to many relationship from MLOBOOK to Data (lots of
experiments can be run on one sample). Each experiment (one data record)
can
be run under multiple conditions (temperature=40C and pressure=1ATM...).
Multiple experiments can be run with the same conditions (Density and
Boilng
Point can both be run at 1ATM but if I changed the condition for one
experiment I wouldn't want that condition to be updated for other
experiments). I have Data and Conditions set up as a many to many
relationship through the ConditionsDataJunction table but I'm wondering if
it
would be better as a 1 to many. Should I add the DataID to the Condtions
table, eliminate ConditionsDataJunction, and make it a 1 to many? If I do
this I know I'll have lots of conditions records for Temperature, 40, C
with
different DataID. Is this a problem? Thanks. I hope this is more clear.
Marcie

.

 

[Jeff Boyce]

It's not me, it's Access ... if you continue to use "Data", you and Access
will come to blows...

I agree (with what, I'm a little fuzzy), the only way I've found in Access
for handling many-to-many relationships (note: these are real-world
relationships) is that third/junction table.

Best of luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Thanks for your comments. I think you've got it. Sorry about "data"
bothering you. In scientific research all results are called data. I'll
call it tblResults for the rest of this conversation. From your comments,
considering update anomilies, and speaking with another scientist here I
think I'm going to keep the relationship as is between tblResults and
tblConditions. There are cases in which multiple results should be linked
to
the same conditions records (ie one experiment produces multiple resutls
and
conditions should be updated together) and there are case in which
multiple
results are run at the same condition but should have different condition
record (ie lots of unrelated experiments are run at 40C and should not be
updated together).
Marcie

I'm sorry, maybe I'm low on caffeine still this morning...

I'm not seeing the situation clearly enough.

What I did notice, though, is what looks like unnecessary duplication.
Why
do you have [CatalogPrefix], [CatalogYear], and [CatalogID] all
duplicated
in your table named [Data] (and by the way, that word may be reserved by
Access -- try renaming that table to something more meaningful and not
reserved).

I'm also a bit concerned about the [Conditions] table. That looks like
you
might be using it to hold temperature conditions, and pressure
conditions,
and ... If true, you would be using the same field to hold different
(*types of*) data, not a standard approach to data structure/design.

It sounds like you are saying that [MLOBOOK] stores sample info, but that
your [Data] (hate that name!) table stores test results. ... and your
ConditionsData table associates test results with conditions.

That all seems reasonable (if I've interpreted correctly).

So, what issues/problems are you facing with using this data structure
(HATE
that table name!)<g>.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Thanks. Here's my tables.
MLOBOOK (this is the sample information table)
CatalogPrefix
CatalogYear
CatalogID (these three are a compound primary Key)
...

Data (This table holds results from scientific experiments.)
ID (PK)
CatalogPrefix
CatalogYear
CatalogID
Property
Method
ResultsNumeric
ResultsText
Units
...

Conditions (This table holds info about conditions used to run an
experiment.)
ID (PK)
ConditionName
Value
Units

ConditionsDataJunction
ConditionsID
DataID

There is a one to many relationship from MLOBOOK to Data (lots of
experiments can be run on one sample). Each experiment (one data
record)
can
be run under multiple conditions (temperature=40C and
pressure=1ATM...).
Multiple experiments can be run with the same conditions (Density and
Boilng
Point can both be run at 1ATM but if I changed the condition for one
experiment I wouldn't want that condition to be updated for other
experiments). I have Data and Conditions set up as a many to many
relationship through the ConditionsDataJunction table but I'm wondering
if
it
would be better as a 1 to many. Should I add the DataID to the
Condtions
table, eliminate ConditionsDataJunction, and make it a 1 to many? If I
do
this I know I'll have lots of conditions records for Temperature, 40, C
with
different DataID. Is this a problem? Thanks. I hope this is more
clear.
Marcie

:

I don't have a clear enough picture of your situation to recommend
table
relationships.

It would probably help to see a bit more of an example and an outline
of
the
table structure. And you know your 'domain' better than we do.

Here's an example (grossly oversimplified) of a data structure for a
"registration" database:

tblStudent
StudentID
FName
LName
DOB
... (other student-specific data)

tblClass
ClassID
ClassTitle
ClassDescription
... (other class-specific data)

trelRegistration
RegistrationID
StudentID
ClassID
RegistrationDate
... (other 'registration'-specific data)

Since one student can sign up for many classes, and one class can have
many
students signed up for it, these two are related many-to-many. To
resolve
this, the third table is needed to show valid pairs.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


My database contains scientific/experiemental data. I have a tables
for
sample information, data results, conditions under which the
experiment
was
run (ie temperature = 40F), and others. Currently I haved a many to
many
relationship between data results and conditions through a join
table.
I
was
thinking about making it a one to many relationship. Any comments
on
this?
I thought about making this change b/c even though data records may
have
been
run under the same conditions they shouldn't be updated at the same
time.
Also I'm trying to make a form that will generate a group of records
to
represent a matrix of data. For example we want to run 3 samples
with
the
same experimental method under 3 different sets of conditions. For
that
situation we need 9 data records and have to type them individually.
I
made
a form with a multiselect list box for the sample IDs and 4 subforms
into
which conditions can be typed. Each subform will contain a unique
set
of
conditions. I think I'll need several append queries to do this but
I'm
not
sure where to start and if I should change my table set up first.
Thanks
for
the help.
Marcie


.

.