Implementing a many-to-many relationship

[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.

 

[tina]

your tables are almost there, but not quite. let me give you a fresh
structure, and you can compare it to yours and see where you need to tweak
yours. first, the relationship analysis: one sample may have many tests
performed on it, and each test may be performed on many samples. that's a
many-to-many relationship, modeled by *three* tables:

tblSamples
SampleID (pk)
SampleName
<other fields that describe a sample, but nothing about tests.>

tblTestTypes
TestTypeID (pk)
TestTypeName
<this table lists all the tests that are available, with perhaps other
fields that describe a test, BUT nothing about a specific instance of any
test being performed, and nothing about samples.>

tblSampleTests (the linking table)
SampleTestID (pk)
SampleID (fk)
TestTypeID (fk)
TestValue

the relationships are
tblSamples.SampleID 1:n tblSampleTests.SampleID
tblTestTypes.TestTypeID 1:n tblSampleTests.TestTypeID

create a form bound to tblSamples, or bound to a query based on tblSamples
BUT do not include either of the other two tables in the query. this is your
mainform, i'll call it frmSamples.

create another form bound to tblSampleTests, or to a query based on
tblSampleTests BUT do not include either of the other two tables in the
query. this is your subform, i'll call it sfrmSampleTests.

open frmSamples in Design view, and add a subform control from the Toolbox
toolbar. in the Properties box, set the control's properties as follows:

Name: ChildTests
SourceObject: sfrmSampleTests
LinkChildFields: SampleID
(this refers to the foreign key field in tblSampleTests.)
LinkMasterFields: SampleID
(this refers to the primary key field in tblSamples.)

open sfrmSampleTests in Design view. do *not* bind the SampleID field to a
control in the form. bind the TestTypeID field to a combobox control. set
the control's properties as follows:

RowSource: SELECT TestTypeID, TestTypeName FROM tblTestTypes ORDER BY
TestTypeName;
ColumnCount: 2
ColumnWidths: 0"; 1"
ListWidth: 1.25"
LimitToList: Yes

when you open frmSamples in Form view, you can add, edit, or delete records
in tblSamples. when you add a sample record, then in the subform you can add
as many test records as you wish, in each record choosing a test type from
the combobox droplist and then entering the test value. because of the links
you set in the subform control's Properties box (see above), Access will
*automatically* assign the primary key value of the sample record to the
foreign key field in each subform record.

hth

 

[Sylvain Lafontaine]

Well, you already know that you must remove the field TestTypeID (FK) from
the table tblSample; your problem here is with the query for the subform: in
the subform, you are editing the joining table tblSampleTestType, so you
must put all the fields of this table in the subquery:

SELECT tblSampleTestType.*, tblTestType.TestTypeName
FROM tblTestType INNER JOIN tblSampleTestType ON tblTestType.TestTypeID =
tblSampleTestType.TestTypeID
WITH OWNERACCESS OPTION;

I have used tblSampleTestType.* to really show you that's
tblSampleTestType.SampleID that must be there in the query for the subform
and not the field tblSample.SampleID. Of course, the primary key
tblSampleTestType.SampleTestTypeID is also missing from your query example.

(Note: if you want to, you can also add the table tblSample with the fields
tblSample.SampleID and tblSample.SampleName to the subquery; however, from
your description of the subform, these are unecessary because you only want
to show the values of the table tblTestType in your subform and not the
values of the table tblSample itself.).

In this way, new fields to the table tblTestType can even be entered/created
directly from the subform.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[dweeber62]

Tina:
What a great set of comments. I am going to recreate what you've
described and play with it until it makes sense to me. This is exactly
what I was looking for. THANKS!

 

[dweeber62]

Sylvain:
Thanks so much for your comments. Between your comments and Tina's, I
hope to get a handle on using many-to-many relationships.
Thanks again!