Problem designing a form envolving many-to-many relationships

[David G.]

First: Sorry for the previous multi-posts. I thought I could include a
gif image of my tables and relationships. I've consolidated all my
pertinent information within. Thanks for any comments/help!

definitions:
1. Material = defines a substance
2. Test Type= defines a test (any given material can have a several
test types.)
3. jtblMaterialTestType = holds the design spec value for each test
type for each Material
4. Sample = a Material extracted at a specified time/date.
5.jtblSampleTestType = holds the measured value for each test type for
each sample.

TABLE STRUCTURE:
The tables starting with "jtbl" are join tables for many-to-many
relationships.

tblMaterial
--tblMaterialID PK
--tblMaterialName string
--tblCategoryID FK
--tblTypeID FK
--tblBrand FK

note: The FK fields above are only for organizing and filtering the
many material records when tblMaterial is used as a record source for
a control.

jtblMaterialTestType
--jtblMaterialTestTypeID PK
--tblMaterialID FK
--tblTestTypeID FK
--jtblMaterialTestTypeTarget number:long

tblTestType
--tblTestTypeID PK
--tblTestTypeName string

tblSample
--tblSampleID PK
--tblMaterialID FK
--tblSampleDate date

jtblSampleTestType
--jtblSampleTestTypeID PK
--tblSampleID FK
--tblTestType FK
--jtblSampleTestTypeValue number:long

Note
1. linked fields have the same field name. ie tblSample.tblMaterialID
FK is linked to tblMaterial.tblMaterialID PK.
2. All links are 1:n

DESIGN GOAL:
My goal is a form with subform that allows records to be added or
edited in the subform (which is set to either datasheet or continuous
forms view). Fields in the form header (or body) are combo boxes whose
values are used to filter the subform's record source.

Main Form (record source = tblSample)
-Header
--a couple of unbound combo boxes linked to tblMaterial fields which
will be used in an SQL statement for the cboSample combo box control
in the form detail section.

-Detail
--cboSample combo box. bound to tblSampleID, record source=
tblMaterial where field values match the combo box values located in
the form header.
--other text box, or combo box controls bound to fields in the
tblSample table used to uniquely identify a sample.
--sub form control. record source ????(something like a combined
jtblMaterialTestType and jtblSampleTestType)
If I used jtblMaterialTestType and child/parent linked
jtblMaterialTestType.tblMaterialID to tblSample.tblMaterialID I would
get a list of test types and their target values for that sample. I
want this so the user knows what tests are to be performed and what
the expected values should be.

This doesn't allow the user to enter the measured test values.
how can I create a record source for the subform? Is my table design
part of the problem.

Thanks in advance for any comments!
THANKS!
David G.

 

[Richard]

Hi David,

First off I think your going to have a little trouble with information
overload.
These fine fokes can solve a lot of problems for you but...... they are not
going to tackle your whole database all at once. Try and boil it down to (1)
issue..like

DESIGN GOAL:
My goal is a form with subform that allows records to be added or
edited in the subform (which is set to either datasheet or continuous
forms view). Fields in the form header (or body) are combo boxes whose
values are used to filter the subform's record source.

Hope this helps you get more responces.
Richard

 

[dweeber62]

Richard:
I agree, and only provided so much detail for background information.
My issue is the table structure when working with join tables and
many-to-many relationships.

I included my existing table structure as reference. I believe I am
close. I also am concerned that what I want to do may not be feasible
(or perhaps practical).

I'm stuck and was hoping for some inspiration or insight, not a
completed design.

Thanks for taking the time to comment.

Hi David,

First off I think your going to have a little trouble with information
overload.
These fine fokes can solve a lot of problems for you but...... they are not
going to tackle your whole database all at once. Try and boil it down to(1)
issue..like



Hope this helps you get more responces.
Richard

Hidi Ho,
The Dweeber

 

[Michael Gramelspacher]

First: Sorry for the previous multi-posts. I thought I could include a
gif image of my tables and relationships. I've consolidated all my
pertinent information within. Thanks for any comments/help!

definitions:
1. Material = defines a substance
2. Test Type= defines a test (any given material can have a several
test types.)
3. jtblMaterialTestType = holds the design spec value for each test
type for each Material
4. Sample = a Material extracted at a specified time/date.
5.jtblSampleTestType = holds the measured value for each test type for
each sample.

TABLE STRUCTURE:
The tables starting with "jtbl" are join tables for many-to-many
relationships.

tblMaterial
--tblMaterialID PK
--tblMaterialName string
--tblCategoryID FK
--tblTypeID FK
--tblBrand FK

note: The FK fields above are only for organizing and filtering the
many material records when tblMaterial is used as a record source for
a control.

jtblMaterialTestType
--jtblMaterialTestTypeID PK
--tblMaterialID FK
--tblTestTypeID FK
--jtblMaterialTestTypeTarget number:long

tblTestType
--tblTestTypeID PK
--tblTestTypeName string

tblSample
--tblSampleID PK
--tblMaterialID FK
--tblSampleDate date

jtblSampleTestType
--jtblSampleTestTypeID PK
--tblSampleID FK
--tblTestType FK
--jtblSampleTestTypeValue number:long

Note
1. linked fields have the same field name. ie tblSample.tblMaterialID
FK is linked to tblMaterial.tblMaterialID PK.
2. All links are 1:n

DESIGN GOAL:
My goal is a form with subform that allows records to be added or
edited in the subform (which is set to either datasheet or continuous
forms view). Fields in the form header (or body) are combo boxes whose
values are used to filter the subform's record source.

Main Form (record source = tblSample)
-Header
--a couple of unbound combo boxes linked to tblMaterial fields which
will be used in an SQL statement for the cboSample combo box control
in the form detail section.

-Detail
--cboSample combo box. bound to tblSampleID, record source=
tblMaterial where field values match the combo box values located in
the form header.
--other text box, or combo box controls bound to fields in the
tblSample table used to uniquely identify a sample.
--sub form control. record source ????(something like a combined
jtblMaterialTestType and jtblSampleTestType)
If I used jtblMaterialTestType and child/parent linked
jtblMaterialTestType.tblMaterialID to tblSample.tblMaterialID I would
get a list of test types and their target values for that sample. I
want this so the user knows what tests are to be performed and what
the expected values should be.

This doesn't allow the user to enter the measured test values.
how can I create a record source for the subform? Is my table design
part of the problem.

Thanks in advance for any comments!
THANKS!
David G.

Taking a stab at it:

CREATE TABLE Brands (
BrandID AUTOINCREMENT NOT NULL,
BrandName TEXT(50) NOT NULL,
PRIMARY KEY (BrandID)
);
CREATE TABLE MaterialCategories (
MaterialCategoryID AUTOINCREMENT NOT NULL,
MaterialCategoryName TEXT(50) NOT NULL,
PRIMARY KEY (MaterialCategoryID)
);
CREATE TABLE MaterialTypes (
MaterialTypeID AUTOINCREMENT NOT NULL,
MaterialTypeName TEXT(50) NOT NULL,
PRIMARY KEY (MaterialTypeID)
);
CREATE TABLE Materials (
MaterialID AUTOINCREMENT NOT NULL,
MaterialName TEXT(50) NOT NULL,
MaterialCategoryID LONG NOT NULL REFERENCES
MaterialCategories (MaterialCategoryID),
MaterialTypeID LONG NOT NULL REFERENCES
MaterialTypes (MaterialTypeID),
BrandID LONG NOT NULL REFERENCES
Brands (BrandID),
PRIMARY KEY (MaterialID)
);
CREATE TABLE TestTypes (
TestTypeID AUTOINCREMENT NOT NULL,
TestTypeName TEXT(50) NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY (TestTypeID)
);
CREATE TABLE MaterialTestTypes (
MaterialTestTypeID AUTOINCREMENT NOT NULL,
MaterialID LONG NOT NULL REFERENCES
Materials (MaterialID),
TestTypeID LONG NOT NULL REFERENCES
TestTypes (TestTypeID),
TargetNumber INTEGER NOT NULL,
PRIMARY KEY (MaterialTestTypeID),
UNIQUE (MaterialID, TestTypeID)
);
CREATE TABLE Samples (
SampleID AUTOINCREMENT NOT NULL,
MaterialID LONG NOT NULL REFERENCES
Materials (MaterialID),
SampleDate DATETIME NOT NULL,
PRIMARY KEY (SampleID)
);
CREATE TABLE SampleTestTypes (
SampleTestTypeID AUTONUMBER NOT NULL,
SampleID LONG NOT NULL REFERENCES
Samples (SampleID),
MaterialTestTypeID LONG NOT NULL REFERENCES
MaterialTestTypes (MaterialTestTypeID),
TestValue INTEGER NOT NULL,
UNIQUE (SampleID, MAterialTestTypeID),
PRIMARY KEY (SampleTestTypeID)
);