many to many relationship help

J

Jessica

Hello All,


I am so confused and can't quite understand Access help. Is there a site
that better explains on how to set up a many to many relationship
between two tables. Do I have to enter in the whole record again if one
of the fields contains more than one record from the other table.


TIA,
Jess
 
A

Allen Browne

See:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html
for an explanation of how to resolve a many-to-many relationship.

It is *really* important to learn this, so take your time, walk through some
examples, and figure it out.
 
J

Jeff Boyce

Jessica

Students and Classes.

One Student can register for many classes.

One Class can have many students registered.

There's two tables (Students, Classes) in a many-to-many relationship.

To handle this, you need to create a third table to hold valid combinations
of Students and Classes. After all, you don't have Students in every Class,
nor all Students in a single Class.

A simplified example might be:

trelRegistration ("trel" for table-relationship or junction table, or
resolver table)
RegistrationID
StudentID (a foreign key, pointing back to the row in tblStudent)
ClassID (a foreign key, pointing back to the row in tblClass)
... (other registration-specific facts -- e.g., RegistrationDate)

Good luck

Jeff Boyce
<Access MVP>
 
T

Todd Shillam

Jessica said:
Hello All,


I am so confused and can't quite understand Access help. Is there a site
that better explains on how to set up a many to many relationship between
two tables. Do I have to enter in the whole record again if one of the
fields contains more than one record from the other table.


TIA,
Jess
Click to expand...


Jess,

Take your time like the others have stated--this is important, and it will
save you (and whom you are helping) a lot of time (especially doing the data
entry and maintenance. If you need help, just keep asking questions.

Best regards,

Todd
 
Top