zchenxin
asked on
Many to many relationship
Hi,
For Eg I had a student table and an activity table. 1 student can have many activities and 1 activity can have many student. This make up a many to many relationship.
I had a insection table Student_Activity. When i am creating my subform i am using the student table as the subform and activity table as the form. However I am not allow to have duplicate key in my parent key. So i should be using the insection table right?
Another thing is how do i add the parent key then cos if i se the intersection table value i should add the parent table first right?
Also do i find the form properties and change the default view to continuous forms like what mrtgold has say?
For Eg I had a student table and an activity table. 1 student can have many activities and 1 activity can have many student. This make up a many to many relationship.
I had a insection table Student_Activity. When i am creating my subform i am using the student table as the subform and activity table as the form. However I am not allow to have duplicate key in my parent key. So i should be using the insection table right?
Another thing is how do i add the parent key then cos if i se the intersection table value i should add the parent table first right?
Also do i find the form properties and change the default view to continuous forms like what mrtgold has say?
I'm going to guess from the sound of it that you're using MS Access for you database and GUI, please correct me if that's not the case.
Yes, having a third table that can associate students and activities is the right way to go. Use that table as the basis for a subform. In Access, you can open the properties window with the subform selected and specify the Link Child and Link Master fields. For the student form (when the subform should list that student's activities) use the StudentID as both the Link Child and Master fields. That should get what you want.
Yes, having a third table that can associate students and activities is the right way to go. Use that table as the basis for a subform. In Access, you can open the properties window with the subform selected and specify the Link Child and Link Master fields. For the student form (when the subform should list that student's activities) use the StudentID as both the Link Child and Master fields. That should get what you want.
Student will have a Primary Key
Activity will have a Primary key
Student_activities will have no keys
Student -> Student_activities (field1), Student_activities (filed2) -> Activity
Paul
Activity will have a Primary key
Student_activities will have no keys
Student -> Student_activities (field1), Student_activities (filed2) -> Activity
Paul
ASKER
But where do i find the subform properties in MS Access 2000 and change the default view to continuous forms like? As the default view is in datasheet format. the tables look like follow like what mlmcc had say but I had one more year table.
This year table is my main form. The activites is my subform. and the student table a nested subform within activites table.
I need to ensure that the StudentToActivity table had 3 of its attribute updated but i only manage to get the StudentID and Activity ID updated what should i do?
Year Tables
Year
Activities
Activity ID
Actvity Name/description
Students
Student ID
Student Name and other info
StudentToActivity
Student ID
Activity ID
Year
This year table is my main form. The activites is my subform. and the student table a nested subform within activites table.
I need to ensure that the StudentToActivity table had 3 of its attribute updated but i only manage to get the StudentID and Activity ID updated what should i do?
Year Tables
Year
Activities
Activity ID
Actvity Name/description
Students
Student ID
Student Name and other info
StudentToActivity
Student ID
Activity ID
Year
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad I could help
mlmcc
mlmcc
Activities
Activity ID
Actvity Name/description
Students
Student ID
Student Name and other info
StudentToActivity
Student ID
Activity ID
Use the Activities table on the main form
StudentToActivity to select from Students on the subform
mlmcc