Introduction

Welcome! Many-to-Many & Junction Tables

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

Welcome to Microsoft Access Expert Level 7. In this course we will explore many-to-many relationships in Microsoft Access, starting with common mistakes using the Lookup Wizard and then moving on to the correct approach using junction tables. We will walk through examples relating customers to groups, building forms to view and assign these relationships, creating mail merge letters for selected groups, and managing duplicate values. We will also discuss formatting reports to print postcards and demonstrate another many-to-many example with vendors and products, including tracking unit costs for each. Questions and support resources are available throughout the course.

Navigation

Prev: X07
Next: Lesson 01

Keywords

Access Expert, many to many relationships, junction table, customer groups, vendor products, lookup wizard, relationship database, duplicate values, report formatting, letter writer, mail merge, postcard report, unit cost comparison, SQL, Visual Basic, ma

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 7, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class focuses on learning many-to-many relationships. We have seen how one-to-one and one-to-many relationships work in previous classes. Today we will focus on many-to-many, which is a little bit more difficult.

We will begin by learning about the wrong way to set up a many-to-many relationship using the Lookup Wizard that Access has directly in the tables. You are going to learn the wrong way first to get a better appreciation for how to set up many-to-many relationships the right way.

We will learn the right way to set up many-to-many relationships with a couple of different examples. We will start by creating a relationship between groups and customers. Multiple customers can be in a group, and multiple groups can belong to a single customer. We will learn about something called a junction table, which bridges the gap between two other tables.

We will build a series of forms so you can see each of your customers and what groups they belong to, and each of your groups and which customers are in that group. We will then make a letter that you can send out by selecting multiple groups so you can send the letter to everyone in your hardware, software, and training groups. We will also learn how to manage duplicate values.

By popular request, we will see how to format a report to print postcards. We will learn some new manual report formatting techniques. Finally, we will go through a second example of a many-to-many relationship relating vendors to products, where each vendor can provide multiple products and each product can be purchased from multiple vendors. We will set up a unit cost for each vendor for each product so you can compare prices and purchase from the cheapest vendor.

This class follows Access Expert Level 6. It is strongly recommended that you watch Level 6 and all the previous classes before proceeding with today's class. In Level 6, we design the letter writer form and tables that are used in today's class. Level 6 covers sending mass mail, collection letters, letters to customers by category, and lots more. To purchase a copy of Level 6, visit my website at AccessLearningZone.com.

This course was designed to be used with Access 2013. However, if you are using Access 2010 or 2007, you should not have any problems following along, as all three versions are pretty similar. If you do happen to come across something that does not work with 2007 or 2010, please send me an email or post in the forums and let me know.

If you are using Access 2003 or earlier, you should still be able to follow along with this class. The material on relationships really has not changed since the earliest versions of Access. Now, I have decided to include this material earlier in my course than I did the last time I recorded this Access Series back for Access 2003. Previously, I covered this material in my Advanced Class Access 309, so if you want to use the 2003 version, that is the course you will have to get. However, give this class a try first. You should be able to follow along with 2003 or earlier.

Of course, if you have any problems or questions, please feel free to let me know. You are strongly encouraged to build the database that I build in class as the best way to learn. However, if you are lazy like me, go ahead and download it from my website at accesslearningzone.com/databases.

My courses are broken up into four groups: Beginner, Expert, Advanced, and Developer. The Beginner lessons are designed to give you a basic overview of the simple features of Access. The Expert Series, the one you are in now, is designed for more experienced users who are already comfortable with Access. The Advanced lessons cover working with macros, automation, and many more advanced features. The Developer lessons get into programming with Visual Basic for Microsoft Access.

Each of the Series is broken down into different numbered levels, starting with Level 1; the Beginner Series, for example, had Levels 1 through 9. In addition to my normal Access classes, I also have seminars designed to teach specific topics.

Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders, tracking accounts payable, learning the SQL programming language, and lots more. You can find complete details on all these seminars and more on my website at AccessLearningZone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course using my custom video player software or the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class videos, as long as you have an active internet connection. Here you will see all of the questions that other students have asked, as well as my responses to them, and comments that other students may have made. I encourage you to read through these questions and answers as you start each lesson and feel free to post your own questions and comments as well.

If you are not watching the lessons online, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend that you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from this class.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum. Most importantly, keep an open mind. Access might seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Now, let us take a closer look at exactly what is covered in today's class.

In lesson one, we are going to see how to set up lookup values directly in tables, both single-value lookups and multiple-value lookups, and we are going to learn why you should not do this.

In lesson two, we are going to learn the proper way to set up a many-to-many relationship between customers and groups using a junction table.

In lesson two, we learn how to view what groups a customer was in. Now in lesson three, we will learn how to open up a group and see which customers are in that group.

Now that we have our groups set up and we have our customers assigned to groups, in lesson four, we are going to create a subform on a letter form so we can generate letters to the selected groups.

In lesson five, we are going to learn some new manual report design techniques and generate postcards.

In lesson six, we are going to see another example of a many-to-many relationship with vendors to products, where each vendor will sell you multiple products and each product can be purchased from multiple vendors.

Quiz

Q1. What is the main focus of Microsoft Access Expert Level 7?
A. Learning many-to-many relationships
B. Using macros to automate tasks
C. Designing web-based databases
D. Advanced VBA programming

Q2. Which tool in Access is referred to as the WRONG way to set up many-to-many relationships?
A. Lookup Wizard in tables
B. Junction Table
C. Report Wizard
D. Form Wizard

Q3. What is the key component needed to properly create a many-to-many relationship in Access?
A. Parameter query
B. Lookup field
C. Junction Table
D. Crosstab query

Q4. In the example from the lesson, what two entities are first related using a many-to-many relationship?
A. Vendors and Products
B. Customers and Groups
C. Employees and Departments
D. Orders and Products

Q5. What is one of the forms created in class designed to do?
A. View customers and their assigned groups
B. Calculate total sales tax
C. Track employee attendance
D. Generate inventory reorder reports

Q6. What is the benefit of using a junction table in a many-to-many relationship?
A. It creates a bridge between two tables allowing multiple records in both tables to relate to each other
B. It automatically generates reports
C. It increases database performance by eliminating all relationships
D. It is required for one-to-one relationships

Q7. What additional example of many-to-many relationships is covered later in the class?
A. Hardware and software categories
B. Vendors to products with unit costs for each
C. Employees to projects
D. Orders to invoices

Q8. Why does the instructor tell students to learn the wrong way to set up many-to-many relationships first?
A. To help students appreciate the correct method
B. There is no right way
C. It is easier to use in all situations
D. It is the only way supported in Access 2003

Q9. What extra feature is demonstrated for sending mass mail?
A. Creating a query for all customers
B. Sending letters to selected groups
C. Linking to Outlook contacts
D. Automating the Mail Merge Wizard

Q10. What new report formatting technique is introduced in this class?
A. Report grouping
B. Formatting report to print postcards
C. Subtotals in reports
D. Data validation in reports

Q11. According to the video, what should you do if you are stuck or do not understand something?
A. Guess the answer and move on
B. Watch the video again or ask questions in the student forum
C. Skip the lesson entirely
D. Hire a freelance developer to fix it

Q12. What does the instructor recommend is the best way to learn from the course?
A. Read the transcript multiple times
B. Purchase a printed manual
C. Build the database step by step as demonstrated in the videos
D. Memorize the quiz answers

Q13. In which earlier class is the letter writer form and tables first created?
A. Access Beginner Level 1
B. Access Expert Level 6
C. Access Advanced Level 2
D. Access Developer Level 1

Q14. What is the recommended sequence for watching the classes?
A. Watch in any order
B. Watch Level 7 before previous levels
C. Watch Level 6 and earlier before Level 7
D. Start with Advanced then go to Beginner

Q15. If you are using Access 2003 or earlier, what does the instructor suggest?
A. Do not proceed, as it is impossible
B. Reference Access 309 for the 2003 version, but try to follow along with this class first
C. Upgrade to Access 2013 before starting
D. Only use Lookup Wizards for all relationships

Answers: 1-A; 2-A; 3-C; 4-B; 5-A; 6-A; 7-B; 8-A; 9-B; 10-B; 11-B; 12-C; 13-B; 14-C; 15-B

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 focused on understanding and working with many-to-many relationships in Microsoft Access. I am Richard Rost, your instructor, and in this lesson, I am going to help you navigate some of the more advanced features available in Access, particularly those that apply when we need to allow multiple records in one table to be related to multiple records in another.

We have already covered one-to-one and one-to-many relationships in earlier classes. Many-to-many relationships are a bit more complicated, but they are important to understand for building robust databases. To start, I will walk you through the wrong approach: using the Lookup Wizard inside your tables. Even though the Lookup Wizard might seem convenient, building relationships this way can lead to data integrity issues and make your database harder to maintain. I want you to see and understand the limitations here, which will make the right way much clearer.

After exploring what not to do, I will guide you through the proper way to build a many-to-many relationship. Our first example will involve customers and groups. In this scenario, a customer can belong to several groups, and each group can have multiple customers. To manage this, we will create what is called a "junction table," which acts as a bridge between the two related tables. This allows us to handle many-to-many connections cleanly and efficiently.

We will also create forms to easily see which groups each customer belongs to, and on the flip side, to display which customers are members of each group. I will show you how to generate a letter that can go out to everyone in selected groups at once, such as hardware, software, or training groups. We will address how to avoid duplicate values as well.

By request, I will also demonstrate how to format a report so you can print postcards, covering new manual report-formatting skills along the way. On top of that, I will include a second practical example: managing relationships between vendors and products. Here, every vendor might supply several products, and each product can be purchased from multiple vendors. You will learn how to set up a system to track the unit cost for each vendor-product combination, making it easy to compare prices and choose the most cost-effective supplier.

This class builds directly on Access Expert Level 6, so I strongly encourage you to review Level 6 and the earlier courses first if you have not done so already. Level 6 covered the basic letter writer forms and tables that we will use in this class, along with mass mailing, collection letters, and more. If you want to purchase Level 6, just visit my website at AccessLearningZone.com.

I designed this course primarily for Access 2013, but if you are using Access 2010 or 2007, you should be just fine following along. The features we use are consistent across these versions. If you run into anything that does not work exactly the same in Access 2007 or 2010, just let me know by email or through the forums.

For those of you still working in Access 2003 or even earlier versions, you should be able to follow most of the content here as the basics of relationships have not changed much over the years. I have included this material earlier in my current course than I did in previous series, so if you need the old material, check out my Advanced 309 class for Access 2003, but go ahead and try these lessons first.

If problems come up or you have questions, please do not hesitate to ask. I highly recommend that you build the database along with me. This hands-on practice is the best way to understand the concepts and techniques. However, if you prefer, you can simply download the sample database from my website at accesslearningzone.com/databases.

My entire curriculum is divided into four main groups: Beginner, Expert, Advanced, and Developer. The current Expert Series is for experienced users ready to expand their knowledge. Earlier, in the Beginner Series, I covered the essentials for those new to Access. In the future, you may want to look into the Advanced and Developer classes, which move into macros, automation, and Visual Basic programming.

Each series contains several levels, starting at Level 1 and moving up. Besides my regular classes, I also offer focused seminars on building web-enabled databases, working with images and attachments, formatting forms and reports like calendars, securing your data, accounts payable, writing work orders, SQL language fundamentals, and more. All these seminars are detailed on my website.

If you want to discuss the material with other students or get help with tricky topics, check out the student forums on my website. If you are using my custom video player or watching online through my site, the forum for each lesson appears alongside the video, and you should see questions and answers from other students as well as my responses. It is a good idea to read through these as you go and post your own questions as needed.

If you are not watching online, you can still participate in the forums at accesslearningzone.com/forums.

To get the most from each lesson, I suggest you first sit back and watch the entire lesson without doing anything on your computer. Afterwards, start from the beginning and go through the examples step-by-step, building the same database I demonstrate. Do not try to apply these techniques to your own projects until you have fully mastered the sample database. If you get stuck, try watching the video again from the beginning or ask for help in the forums. Remember, Access may seem complex at first, but with practice, it becomes much easier.

Here is a breakdown of what we will cover today:

In lesson one, I will show you how to set up lookup values in your tables, discuss single-value and multiple-value lookups, and explain why using these directly in your tables is not a good practice.

Lesson two will cover the correct way to build a many-to-many relationship between customers and groups using a junction table.

In the following lesson, you will learn to view which groups a customer is part of, and then how to view the customers in any given group.

Once groups and customers are set up, lesson four will guide you through creating a subform on a letter form so you can generate letters to selected groups.

In lesson five, I will introduce some new manual report design tips and walk you through generating postcards for your contacts.

Finally, lesson six will use a real-world vendors-to-products example to show another many-to-many relationship, where each vendor can offer multiple products and each product can come from multiple vendors, including how to track and compare unit costs to decide where you buy.

For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.

Topic List

Setting up lookup values directly in tables
Single-value lookups in Access tables
Multiple-value lookups in Access tables
Why not to use the Lookup Wizard
Understanding many-to-many relationships
Creating a junction table
Building a customer-groups many-to-many relationship
Viewing what groups a customer belongs to
Viewing customers in each group
Creating a subform to select groups for letters
Generating letters to selected groups
Managing duplicate values in many-to-many relationships
Manual report formatting techniques
Printing postcards from grouped records
Vendors and products many-to-many relationship example
Setting unit costs for vendor-product pairs
Comparing vendor prices for products

Article

Welcome to this expert-level guide on Microsoft Access, where we will focus on many-to-many relationships in databases. Before diving in, it is essential that you have a solid understanding of one-to-one and one-to-many relationships, as these are the foundation for grasping the more complex many-to-many setup.

A many-to-many relationship occurs when multiple records in one table are related to multiple records in another table. For example, consider a scenario where you have groups and customers. A single customer can belong to many groups, and each group can have many customers. This situation cannot be handled simply by adding a lookup field in either table, which is a common mistake.

Many beginners use the Lookup Wizard in Access to try and set up these relationships. Access allows you to set lookup fields directly in your tables, creating either single-value or multi-value lookups. While this may seem convenient, it actually leads to problematic database design. Storing multiple values in a single field, or creating hidden relationships using lookups, causes issues with data integrity, query logic, and future database management. You should avoid using lookup fields for many-to-many relationships.

The correct way to set up a many-to-many relationship is by using a junction table, sometimes called a linking or bridging table. The junction table sits between the two tables you wish to relate. It contains at least two foreign keys: each one points to the primary key in one of the tables you want to connect. For example, to relate customers and groups, you would have three tables: Customers, Groups, and a junction table, say CustomerGroup. The CustomerGroup table might have a CustomerID field linking to Customers and a GroupID linking to Groups. Each combination of CustomerID and GroupID in this table represents that the customer is a member of the group.

To make practical use of this setup, create queries and forms that allow you to see, for example, all the groups a customer is in, or all customers in a particular group. On a customer form, you could have a subform showing all the groups for that customer, and similarly, on a group form, you could show all the members.

Suppose you want to send a letter to all customers in specific groups. You can create a subform on your letter form to select multiple groups. Then, use a query that finds all customers belonging to those groups by joining the customers, groups, and your junction table. This makes it easy to do targeted mailings or communications.

Another practical tip is handling duplicate data. For example, make sure your junction table does not contain duplicate combinations of the two foreign keys, which would indicate a customer being assigned to the same group more than once.

A common request is how to format a report for mailing labels or postcards. Access reports let you customize layouts for various mailing needs. You can group by recipient, set up the size and arrangement of labels or postcards, and include any fields you need, such as address or group membership.

To further illustrate many-to-many relationships, consider the example of vendors and products. A vendor can sell many products, and each product can be supplied by many vendors. As before, set up a Vendors table, a Products table, and a VendorProduct junction table with VendorID and ProductID fields. You might also add a UnitCost field to the junction table, representing the price a vendor charges for a specific product. This setup lets you compare prices between vendors easily and choose the most cost-effective supplier.

While this tutorial uses Access 2013, the concepts and steps also apply to previous versions like Access 2010 and 2007, since the way relationships work has not changed much in recent years. Even if you use Access 2003 or older versions, the principles still apply, though the interface might look different.

When learning these concepts, it is best to create the sample databases yourself step by step. First, watch or read through an example completely, then try to reproduce the steps on your own system. Focus on understanding the logic of how relationships are structured and practice designing your own junction tables.

If you run into any problems, review your table structures and relationships. Make sure that foreign keys are properly set and that your queries reference the correct fields. If you want to further explore, consider scenarios like students enrolled in courses, orders containing products, or books with multiple authors. All these situations call for a junction table, and mastering this design pattern is crucial for effective relational database development in Access.

In summary, remember not to use multi-valued lookup fields in your tables for many-to-many relationships. Always use a junction table, relate your data with clear foreign keys, and build queries and forms around that design. This approach results in clean, maintainable, and flexible Access applications, ready to handle real-world complexity confidently.