If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Implementing a many-to-many relationship
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 he 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. |
#2
|
|||
|
|||
Implementing a many-to-many relationship
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 wrote in message ... 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 he 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. |
#3
|
|||
|
|||
Implementing a many-to-many relationship
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) wrote in message ... 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 he 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. |
#4
|
|||
|
|||
Implementing a many-to-many relationship
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! On Fri, 10 Jul 2009 03:13:16 GMT, "tina" wrote: 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 wrote in message .. . 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 he 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. |
#5
|
|||
|
|||
Implementing a many-to-many relationship
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! On Thu, 9 Jul 2009 23:47:10 -0400, "Sylvain Lafontaine" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|