?
Solved

Many to many relationship

Posted on 2003-03-23
6
Medium Priority
?
367 Views
Last Modified: 2008-02-26
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
Comment
Question by:zchenxin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 8193139
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
 
LVL 6

Expert Comment

by:TheAmigo
ID: 8193159
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
 
LVL 1

Expert Comment

by:Paulmul
ID: 8196057
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:zchenxin
ID: 8207284
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
 
LVL 101

Accepted Solution

by:
mlmcc earned 200 total points
ID: 8207625
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 8221409
Glad I could help

mlmcc
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question