Many to Many

Welcome! Many-to-Many: Classes & Students

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this lesson, we will walk through the concept of many-to-many relationships in databases using a classes and students example. I will show you how students are linked to classes through a junction table, explain how class types and individual class sessions are set up, and demonstrate adding and managing class records with attendees from different companies. We will discuss the purpose of each table and subform, how the relationships work, and why this setup is often challenging to grasp. This lesson aims to clarify many-to-many relationships by building the example from scratch and reviewing the underlying structure.

Navigation

Prev: X15
Next: Lesson 01

Keywords

Access Expert, many-to-many relationship, database design, junction table, classes and students, class types, attendees, customer table, unique class, subform, class ID, class type ID, auto number, linking tables, database example

Subscribe to Many to Many
Get notifications when this page is updated

Intro

Transcript

Toddie folks, this is an addendum video that goes out to Kenneth because he is having a little hard time understanding the concept behind this database. This is not an easy concept to get. This is a many-to-many relationship. This is for classes and students.

Now, the students are coming from customers. The customers are companies, and then we put these students' names in here, whatever date they enrolled. The class itself up here is a unique class.

Let me delete the data in the tables. We have class T. This is the list of classes. Let's get rid of those.

Let's get rid of the class types. Forget all these class types that are in here already, because we are going to start with new ones.

Class X customer is the junction table that links the two together. Let's get rid of that.

Starting from scratch, we are going to open up the classes form.

Now I want to put a new class. Let's say I want to teach Microsoft Excel Beginner. I need to create a class type for it first. Let's come over here to the class types. The type of class is Microsoft Excel Beginner. Let's say I also teach an advanced class: Microsoft Excel Advanced. I also teach, let's say, piano lessons.

Those are the three different classes I teach. That could be on different days of the week. It could be whatever. It does not matter. Those are the topics that I teach. That is what a class type is.

Save changes. Yes.

Now that I have those classes, those class types set up in this list, I can start creating actual classes.

Let's pretend that my classes are one-off. It is just one session and you are done. It is not like a Monday, Wednesday, Friday thing.

Let's say I am going to teach an Excel Beginner class. We will just say, Excel Beginner. It is going to be on this one date on 5/15. That is today.

Now, down here, I am going to list whoever will be the attendees. Let's say that 123 Packing sent Joe and Element OP sent Sue.

Two students. This one class on this date. That is it. It is done.

Now, let's say I am going to teach another class on Friday the 17th. Different people. I am going to go create a new record. New class ID. The class type is listed. It is linked to the class type ID, which is a different table.

I am going to also teach Excel Beginner. Whatever you want in the description: Excel Beginner. I do not care. This time it is going to be on the 17th, so Friday.

This time, it is going to be me. I am going to send me. Williams Corp is going to send Bill. The XYZ Corp is going to send Amy.

So class ID is 6. This class ID was 5. These are two separate classes. See here, in the classes, there are two separate classes. This is the one on Wednesday. This is the one on Friday.

Come back over here. Here is the Wednesday one. Here is the Friday one.

The students are customers. The class type has not changed. I am only doing Excel Beginner. I can add an Excel Advanced class. I can add piano lessons.

Let's say I am teaching piano lessons on Sunday. I have a couple of people that want a piano lesson on Sunday. I go to a new record. I pick piano lessons. Give it a description if you want, Piano on Sunday. Set the date. This one is going to be held on Sunday, so that is going to be the 19th.

Who is taking piano lessons? Let's say Real Estate and Forestry Services. Two people. I have Piano One and Piano Two.

Now we have three separate classes in the database.

Taking a look at the design view: this is bound to the class ID. Each class is a unique auto number. The class type ID is what type of class this is. Is it Excel Beginner? Is it Excel Advanced? Is it a piano lesson? There are only three different types of classes. This has nothing to do with this. They are completely different.

Now the subform is where your attendees are. So if you come into here, the Excel Beginner on Wednesday has these two people. The Excel Beginner on Friday has these three people. The piano lessons has these two people.

This is the tough one right here. The subform is based on the junction table.

You do not need to worry about that ID. Here is the class. There is Excel Beginner on Wednesday, Excel Beginner on Friday. You can see the class ID is stored there. Here is the piano lessons on Sunday. There is the customer ID. We are not really worried about these other things right now. There are the students' first names that are enrolled.

This has nothing to do with this. This is what type of class this is.

I hope this helps you understand. Try building it again from scratch. Start at the first lesson. Start back at lesson one and try building it yourself from scratch. Download the sample database. I just downloaded it myself and you can see it is working perfectly. So, give it another try.

Many-to-many relationships are not easy to comprehend. They are probably one of the hardest types of relationships to remember.

Go watch my many-to-many lessons. I hope this helps. As always, if you have questions, post them in the forums.

Quiz

Q1. What type of relationship is being demonstrated in the video?
A. Many-to-many
B. One-to-one
C. One-to-many
D. Hierarchical

Q2. What is the main purpose of the junction table in this database design?
A. To link classes and students together
B. To store customer addresses
C. To track payments for classes
D. To record class schedules

Q3. In the context of this database, what are the students derived from?
A. Customers (companies)
B. Teachers
C. Class types
D. Products sold

Q4. What is a "class type" in this database system?
A. The subject or topic being taught
B. The location where the class is held
C. The time of day the class is scheduled
D. The instructor of the class

Q5. If you want to create a new class for "Microsoft Excel Beginner," what is the first step?
A. Create a class type for "Microsoft Excel Beginner"
B. Add students to the class
C. Enter the class schedule
D. Assign a teacher to the class

Q6. What does the "subform" in the classes form display?
A. The list of attendees for each class
B. The available class types
C. The instructor schedule
D. The payment status

Q7. In this design, where are the attendees for each class stored?
A. In the junction table
B. In the class types table
C. In the classes table
D. In a separate instructors table

Q8. Why is it challenging to understand many-to-many relationships in database design?
A. Because each record in one table can relate to multiple records in another, and vice versa
B. Because there can only be one record related to another
C. Because it does not require a junction table
D. Because it only works with one type of data

Q9. If you wanted to add a new class that is a one-time piano lesson on Sunday, what would you do?
A. Add a new class record with class type "Piano Lessons" and set the date to Sunday
B. Add a new customer
C. Modify the existing class types
D. Delete previous class records

Q10. What should you do if you are having trouble understanding or constructing this database?
A. Watch the many-to-many lessons and start from lesson one
B. Skip to advanced topics
C. Only use one-to-many relationships
D. Contact the database software manufacturer

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is an addendum to help clarify the concept of many-to-many relationships, specifically for a scenario involving classes and students. This can be a challenging topic to grasp, especially when you're just starting out with database design.

In this example, we're working with classes and students, where a single class can have multiple students, and a student can attend multiple classes. To set the stage, the students actually come from a list of customers. In this context, the customers are actually companies, and we enter the names of students when they enroll.

There are multiple tables involved here. We have a table for classes, which lists each individual class as a unique record. We also have a table for class types, which describes the different topics we might teach. For instance, class types could include Microsoft Excel Beginner, Microsoft Excel Advanced, or Piano Lessons. Each class type is simply a category, not a scheduled event.

To start fresh, we remove all existing data from the tables: clear out any records in the classes table, remove all existing class types, and empty the junction table that links classes to customers (students). This junction table is crucial because it creates the many-to-many relationship between classes and the students who attend.

Once the tables are reset, we begin by creating new class types. For example, let's add Microsoft Excel Beginner, Microsoft Excel Advanced, and Piano Lessons as our current class types. These represent the different kinds of classes that might be offered.

With the class types in place, we can now start adding actual classes. In this example, let's say each class session is a single, standalone event rather than a recurring series. For our first class, we'll schedule an Excel Beginner class on May 15th.

Next, we record which students will attend. For this session, let's say two students: Joe from 123 Packing and Sue from Element OP. These are tracked by linking their respective company records in our system.

If we want to offer another Excel Beginner class on a different date, say Friday the 17th, we create a new class record. On this day, perhaps I'm attending, Williams Corp sends Bill, and XYZ Corp sends Amy. Each class has its unique ID, date, and list of attendees, even though they might be the same type.

Let's add an example in another category. Maybe I have a Piano Lesson scheduled on Sunday the 19th. Two attendees for this one: representatives from Real Estate and Forestry Services, marked in the database with the appropriate names.

Now, we have three separate classes, each uniquely recorded in the system, associated with their class types and dates, and each having its own list of students in attendance.

Looking briefly at the structure behind all this, the main class table has fields like ClassID (as an autonumber for unique identification), a foreign key to ClassTypeID, and a date. The subform tied to a class record displays its attendees, pulling from the junction table linking each class to its students. This subform is the key to managing the many-to-many relationship. When you view a class, you can see exactly who attended based on records in the junction table.

Remember, the class types are simply categories or topics; they are separate from the calendar events representing individual classes. Each class is a standalone occurrence with its own set of students.

If you're struggling with this concept, I encourage you to go back to the earlier lessons and try rebuilding the database from scratch. Download the sample database and work through it on your own. Many-to-many relationships are definitely one of the trickier aspects of relational database design, but with practice and hands-on experience, they become much easier to understand.

To further reinforce these concepts, make sure to watch my specific lessons on many-to-many relationships.

If you have any further questions or run into trouble, please post them in the forums. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Understanding many-to-many relationships in databases
Using a junction table to link classes and students
Setting up class types for different course offerings
Creating classes and assigning class types
Enrolling students from customer companies into classes
Entering and managing class records
Assigning students to specific class sessions
Designing forms for class and attendee entry
Explaining the relationship between classes, class types, and customers
Viewing attendee lists for each class via subforms
Differentiating between class types and individual class sessions

Article

Understanding a Many-to-Many Relationship in a Classes and Students Database

Many-to-many relationships can be tough to grasp, especially when you are just starting out with database design. In this tutorial, I will walk you through how to set up a simple database that manages classes and students, where students come from customer organizations. This approach is used when you want to track which students from which companies attend which classes.

The scenario is as follows. Imagine you offer different types of classes such as Microsoft Excel Beginner, Microsoft Excel Advanced, and Piano Lessons. Your customers are companies, and each company can send one or more students to any of your classes. Classes themselves are individual sessions on specific dates. You want to track exactly which students from each company went to which classes. To accomplish this, you need a many-to-many relationship: one class can have many students, and one student can attend many classes.

To build this, you need a few tables. The first is a table of class types, which simply lists the kinds of classes you offer: for example, Excel Beginner, Excel Advanced, Piano Lessons. This table helps you keep your class types organized and makes your database flexible in case you decide to add more types in the future.

Next, you have a table for actual classes. Each record in this table represents a single offering of a class type, held on a specific date. For instance, you might offer a Microsoft Excel Beginner class on May 15, and then another session of the same class type on May 17. Each class gets a unique class ID (usually an autonumber), along with details such as class type and date.

Since your students come from customer organizations, you also have a customers table, where each customer is a company. When a company sends a student to a class, you link the student to the class. Here is where the many-to-many relationship comes in.

To connect classes and students, you need a junction table (sometimes called a linking table or associative table). This table, often named something like ClassXCustomer, contains at least two fields: the class ID and the customer ID. Each record in this table represents one student from one company attending one class. If "123 Packing" sends Joe to Excel Beginner on May 15, you make an entry that links that student, their company, and the class.

Let us see an example in action. First, create the class types: Microsoft Excel Beginner, Microsoft Excel Advanced, Piano Lessons. Save these in the class types table.

Now, create a new class. Suppose you plan to teach the Excel Beginner class on May 15. In the classes table, create a new entry selecting that class type, and set the date. Your classes table now has one class: Excel Beginner on May 15.

Next, list attendees. Suppose 123 Packing sent Joe and Element OP sent Sue. In the ClassXCustomer table, add two records: one linking Joe from 123 Packing to the class, and one linking Sue from Element OP. Now your database knows exactly who attended that class.

Suppose you are offering another beginner class on May 17. Add a new class record for that date with the same class type. This time, say Williams Corp sends Bill, XYZ Corp sends Amy, and you also attend. Record each attendee in the junction table by linking the new class ID to each attendee's customer and name. Each class remains distinct, even if the topic is the same.

Now, you decide to run Piano Lessons on May 19. Create a class record for Piano Lessons with the relevant date. Suppose Real Estate and Forestry Services are sending students. Again, enter the attendees in the junction table.

This setup ensures you can run as many classes as you want, of any type, on any date, and accurately track who attends each session, regardless of how many classes or how many students you have.

Looking at the design, each class record is unique and contains the class type ID, which links back to the class types table. The class type ID simply tells you what kind of class it is, such as Excel Beginner, Excel Advanced, or Piano Lessons. The actual participants are stored in the junction table which links the class ID with student and customer IDs.

You usually set up a form for classes, possibly with a subform that lists attendees. The subform is based on the junction table, so when you open a class record, you see all the students enrolled in that session, along with their customer companies.

When creating or editing records, you do not need to worry too much about the auto-generated IDs that handle the relationships. Just focus on selecting the correct class type, date, and adding the attendee details.

If you find the many-to-many relationship confusing, remember that it is all about linking two tables together through a third table. This setup lets you easily track complex scenarios where multiple students attend multiple classes, and students can be from different companies.

If you want to practice, download a sample database, clear out any demo data, and rebuild these relationships from the ground up. Start by setting up your class types, then add actual classes and finally enroll attendees using the junction table. As you go, you will see how the many-to-many relationship makes it possible to manage this flexible structure. Once you get the hang of this, handling more complex databases will become much easier. If you have questions, there are always forums and resources available to help.