D
dweeber62
Hi all:
I'm having trouble understanding how to implement a many-to-many
relationship. I've studied a few examples I've found on line, but even
after following their instructions I can't get to the point where I
could populate controls on a form.
What I want is a form with combo boxes that identify the sample ID.
Then a subform that displays the various tests performed and the
resulting value. The subform (datasheet view) has to allow for the
addition of tests (from a table of available tests) and editing of the
measurement value.
My last attempt is described here:
tblSample
---SampleID PK
---SampleName
---TestTypeID FK
tblTestType
---TestTypeID PK
---TestTypeName
tblSampleTestType
---SampleTestTypeID PK
---TestTypeID FK
---SampleID FK
I created a multi-field index on TestTypeID with SampleID, called it
"Sample"
(note: I also tried the above without the SampleTestTypeID field,
using the multi-field index as the primary key.)
qrySampleToTestType
(Added all three tables above, with one-to-many relationships between
the primary tables and the junction table. (I deleted the relationship
between tblSample.TestTypeID and tblTestType.TestTypeID)
---tblSample.SampleID
---tblSample.SampleName
---tblSampleTestType.TestTypeID
---tblTestType.TestTypeName
(I had created a few tblSample and tblTestType records by entering
data into the tables directly.)
Ran qrySampleToTestType. No records.
I tried a few things which ended up putting some valid entries in the
junction table.
Ran qrySampleToTestType again. 2 records.
Problems:
1. can't enter a different value for
qrySampleToTestType.-tblSample.SampleID
(Error= ..."bound to AutoNumber field)
2. I could change the qrySampleToTestType.tblSampleTestType.TestTypeID
value for an existing record, but got the following error message if I
tried to type data for a new record.
(Error="....join key of "tblSampleTestType" not in record)
I would really like to find a working sample of a form based on query
involving a many-to-many relationship. Trying to recreate examples in
the books I have has been futile.
Thanks for any help.
THANKS!
David G.
I'm having trouble understanding how to implement a many-to-many
relationship. I've studied a few examples I've found on line, but even
after following their instructions I can't get to the point where I
could populate controls on a form.
What I want is a form with combo boxes that identify the sample ID.
Then a subform that displays the various tests performed and the
resulting value. The subform (datasheet view) has to allow for the
addition of tests (from a table of available tests) and editing of the
measurement value.
My last attempt is described here:
tblSample
---SampleID PK
---SampleName
---TestTypeID FK
tblTestType
---TestTypeID PK
---TestTypeName
tblSampleTestType
---SampleTestTypeID PK
---TestTypeID FK
---SampleID FK
I created a multi-field index on TestTypeID with SampleID, called it
"Sample"
(note: I also tried the above without the SampleTestTypeID field,
using the multi-field index as the primary key.)
qrySampleToTestType
(Added all three tables above, with one-to-many relationships between
the primary tables and the junction table. (I deleted the relationship
between tblSample.TestTypeID and tblTestType.TestTypeID)
---tblSample.SampleID
---tblSample.SampleName
---tblSampleTestType.TestTypeID
---tblTestType.TestTypeName
(I had created a few tblSample and tblTestType records by entering
data into the tables directly.)
Ran qrySampleToTestType. No records.
I tried a few things which ended up putting some valid entries in the
junction table.
Ran qrySampleToTestType again. 2 records.
Problems:
1. can't enter a different value for
qrySampleToTestType.-tblSample.SampleID
(Error= ..."bound to AutoNumber field)
2. I could change the qrySampleToTestType.tblSampleTestType.TestTypeID
value for an existing record, but got the following error message if I
tried to type data for a new record.
(Error="....join key of "tblSampleTestType" not in record)
I would really like to find a working sample of a form based on query
involving a many-to-many relationship. Trying to recreate examples in
the books I have has been futile.
Thanks for any help.
THANKS!
David G.