Re: Why are Junction Tables necessary?

I'll try by way of an example...

You have students. If you want to add another student, you just add him/her
to the tblStudent.

You have classes (i.e., a classroom, a topic, an instructor). If you want
to add another class, just add it to the tblClass.

Now, how do you show which student is enrolled in which class?

You can't really add that as a field in the tblStudent unless you are only
allowing your students to enroll in a single class, and are not interested
in any previous class they took. Ditto for why you couldn't add a student
to the tblClass. But you described a "many-to-many" relationship, so
neither of these is appropriate.

Now create a third table, trelEnrollment. Put one StudentID and one ClassID
in a new row, plus any other "facts" about the enrollment, like
DateEnrolled.

Does it make any more sense with an example?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


"VT" <VT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C421D267-08FD-4E57-8968-E805A1A5FA99@xxxxxxxxxxxxxxxx
I have been reading about databases for just a few hours, so think of me
as a
complete muppet for the purpose of explanation...

I don't understand why many-to-many relationships are not possible, and
why
we need two tables, each with a one-to-many relationship with a third
table.

Thanks for your help.

.

Relevant Pages

  • RE: Changing data in a field using a form
    ... STUDENT_ID - AutoNumber, Primary Key ... STUDENT_ID - Long Integer Links to tblStudent ... look up the student, a text box for the student's name, a text box for the ...
    (microsoft.public.access.formscoding)
  • Re: continous form to find record
    ... Is there a way to find record in tblStudent without setup relationship? ... I just want to enter SSN in tblException and some kind of code to find ... Are there multiple records in tblException for each Student or Multiple ...
    (microsoft.public.access.forms)
  • Re: Junction table question
    ... record for the student exists in TblStudent and a record for the course ... In a lot of the examples they use a student table and a course table. ... Then they create a junction table with the primary keys from each ... (please reply to the newsgroup) ...
    (microsoft.public.access.gettingstarted)
  • Re: Doing my head in
    ... These are for my own classes, so no student appears in any other class ... "Jeff Boyce" wrote: ... > ClassID ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Office 2003 upgrade to 2007 through Upgrade Guarantee Program
    ... Dear Microsoft Office Technology Guarantee Program: ... Will the version of Office Home & Student 2007 upgraded from Office ... Student & Teacher 2003 through the Technology (Upgrade) Guarantee ...
    (microsoft.public.office.misc)