[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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?
0
zchenxin
Asked:
zchenxin
1 Solution
 
mlmccCommented:
I assume you have tables like
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
0
 
TheAmigoCommented:
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.
0
 
PaulmulCommented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
zchenxinAuthor Commented:
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


0
 
mlmccCommented:
You really don't need a Year table unless you intend to put other information in it since the year could be selected other ways.

>>But where do i find the subform properties in MS Access 2000 and change the default view to continuous forms like?
Double click on the subform to open it
Select the FORM - Click upper left corner
Change DATASHEET to Continuous

mlmcc

0
 
mlmccCommented:
Glad I could help

mlmcc
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now