Lessons

Resources

Learn More

Learn more about Normalizing Data

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Comments for Relationship Seminar Lessons

Age	Subject	From
8 months	Potential Course Error	James Chessher
15 months	Join Vs Relationship	Rolf Widmer
2 years	more many to many on form	Ludwig Willems
3 years	Lesson 11 Build Event	Aslyn Stiff
3 years	Reverse Relationships	Ludwig Willems
4 years	Reverse Relationship Delete	David Pierce
4 years	How To	Donald Wilkins
4 years	Btn to Get Another Didn't Work	Sveinn Oskar Sigurdsson

Subscribe to Relationship Seminar Lessons
Get notifications when this page is updated

Intro

In this seminar, you will learn about all the different types of relationships available in Microsoft Access databases, including one-to-many, one-to-one, many-to-many, self-joins, and reverse relationships. We will go step-by-step through building eight sample databases, demonstrating how to set up each relationship type, create forms and subforms, use combo boxes, set up mailing list queries, and implement features like cascade deletes. The lessons also include designing templates for building your own databases and some advanced topics using SQL and VBA for experienced users.

Transcript

Welcome to the Microsoft Access Relationships seminar brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

The purpose of today's seminar is to cover all the different types of relationships that you can set up in a Microsoft Access Database. You will learn how to set up a database incorrectly using no relationships, and then we will cover all the different types of relationships that you will see in an Access Database. You'll learn about one-to-many, one-to-one, many-to-many, and lots more different types of relationships.

Altogether, we'll build eight different databases covering all the different types of relationships in Microsoft Access. These will be good starting templates for building a database of your own. You can download the sample database files from my website, instructions will follow later, or you can build these databases yourself by following along with me in the videos. You'll learn better by following along with me and building the databases yourself, but if you have a project that you have to get off the ground quickly, feel free to use my databases as a starting template.

I've tried to make this seminar so there's something for everyone. Most of the material is fine for beginner and intermediate level students. If you've taken my beginner level Access classes, you should be just fine with this seminar. If you're a complete novice, if you've never used Access before, you should definitely take at least my Level 1, 2, and 3 beginner courses that cover all the fundamentals of setting up a database, building your tables and queries, and forms. But once you're past the basics, you should be okay with this seminar.

A couple of the lessons are for my advanced students. They will include some SQL, some VBA programming. Like I said, I tried to get a little something for everyone in this seminar. To make sure my advanced users aren't bored, there are two or three lessons for them. But if you're still a beginner, don't worry. You can learn the material as you go along, and then when you get to the advanced lessons, hopefully you'll be able to follow those as well.

Throughout the class, I will mention several other tutorials on my website, some are free, that you can watch to bone up on some of the material before I cover it in these classes. I will be using Access 2010 in this seminar. If you have 2007, you shouldn't have any problems following along. If you're using 2003 or earlier, most of the material will work just fine for you, with 2003, or XP, or even Access 2000. All the relationship stuff is the same, it really hasn't changed. The only thing that will be different will be the menu interfaces, the ribbon, where the different commands are located, and so on.

As you're following along with the videos, if you're watching the lessons online, be sure to post any questions that you have in my student forums. There is a forum for each video. If you're using my online theater or my Amicron video player that you can download, you'll see the student forum pops up right next to the video. If you have questions, please post them there.

Now let's take a more in-depth look at exactly what we're going to cover in today's class.

In Lesson 1, I'm going to give you a brief overview of the different types of relationships that are possible in Access, and what you can expect to see covered in the seminar.

In Lesson 2, we're going to take a look at a database that's built incorrectly with no relationships, sometimes also called a flat file database, where we have multiple fields in one table to represent what should be a second table with additional fields there.

In Lesson 3, we'll create a basic one-to-many relationship. We'll move the parent information into its own table, so we can have one student with multiple parents; that's a one-to-many relationship.

In Lesson 4, we'll take our two tables, our student table and our parent table, and create a form with a subform, so for each student, we can see a list of all of their parents right on the student form.

In Lesson 5, we'll take our student and parent form, hide the IDs we don't need to see on the parent form, and create a combo box so we can pick the relative type.

In Lesson 6, we'll set up a query for mailing list purposes, so that all of the parents for each student that are supposed to be on the mailing list to get notifications, will get them. We'll talk about global relationships and cascade deletes, so if you delete a student, all of the parents associated with that student are also deleted.

In Lesson 7, we'll talk about one-to-one relationships, where you have one record in one table and a matching record in another table. That's great if you have information like students, but you don't have all of the information for each student, like address, phone number, email, and all that extraneous information; you can put it in a separate table to save space. There are also some security benefits too, and we'll talk about that in this lesson.

In Lesson 8, we'll start looking at many-to-many relationships. That's where you have many items on the left that can relate to many items on the right and vice versa. For example, products and vendors; you might have a list of products, a list of vendors. The products can be purchased from multiple vendors, and each vendor offers multiple products, so there's a many relationship going both ways. We'll see how this works in this lesson.

In Lesson 9, we're continuing on with the many-to-many relationships that we started in the last lesson. We're going to make a subform for each of our main forms, so the vendors will have a product subform, and the products will have a vendor subform, and you'll see combo boxes for all the different options.

In Lesson 10, we're going to learn about self-joins, where a table can be related to records in itself.

In Lesson 11, we're going to learn another self-join, this time a many-to-many self-join, where you can have a person linked to many other people, a self-join, like all of his relatives.

In Lesson 12, we're going to talk about reverse relationships. This is where I pick mother, and it automatically sets up the relationship for the child as well. So two people will each get a relationship.

In Lesson 13, we're going to make the button that actually saves the relationships in our tables. We're going to learn about after-update events that do lookup functions and some basic SQL.

In this lesson, we're going to start taking some of the techniques we learned in the previous lessons and putting them all together. We're going to start with an organization database that you can use for a company, for a family, for a charity, and the organization is going to have members. The organization is also going to have other one-to-many and many-to-many relationships, too, where you can store different addresses, different phone numbers, a contact history, so you'll see that one organization can have multiple one-to-many and many-to-many relationships and have them all in the same form.

In Lesson 15, we're going to add subforms for addresses and for contact history. For each of our organizations and members, we'll use the same address table to store addresses for both organizations or members, and the same with the contact history table.

Quiz

Q1. What is the main focus of the Microsoft Access Relationships seminar?
A. Learning how to create reports in Access
B. Understanding and creating different types of database relationships in Access
C. Building web applications with Access
D. Learning how to use Excel with Access

Q2. Which of the following is NOT a type of relationship discussed in this seminar?
A. One-to-many
B. One-to-one
C. Many-to-many
D. Entity-attribute-value

Q3. Building a database with no relationships in Access is sometimes referred to as a:
A. Linked database
B. Flat file database
C. Relational database
D. Hierarchical database

Q4. What is the purpose of creating a form with a subform in Access?
A. To display unrelated records together
B. To allow for advanced calculations
C. To easily view related records, such as a student and all their parents
D. To enable data export to Excel

Q5. Why would you use a combo box in a form when working with relationships?
A. To enter textual notes
B. To pick the type of relationship or relative more easily
C. To reduce database size
D. To hide all records from the user

Q6. What is a cascade delete?
A. Automatically exporting data after deletion
B. Having to manually delete records in child tables
C. Automatically deleting all related child records when a parent record is deleted
D. Creating forms with drop-down menus

Q7. A one-to-one relationship is useful for:
A. Storing redundant data in a single table
B. Organizing related records where there may not be information for every record, or for security purposes
C. Allowing multiple records in both tables to relate to each other
D. Automatically updating addresses

Q8. Which scenario best describes a many-to-many relationship?
A. One customer with multiple orders
B. Multiple products can be offered by multiple vendors
C. Each employee assigned to a single department
D. A single product assigned to one vendor

Q9. What is a self-join in Microsoft Access?
A. Relating records from one table to unrelated tables
B. Relating records in one table to records in the same table
C. Joining tables from multiple databases
D. Linking Access with external data sources

Q10. What is described by "reverse relationships" in the seminar?
A. Linking two unrelated tables together
B. When a relationship is set on one side, it is automatically set for the related record as well
C. Undoing all relationships in a table
D. Creating relationships with deleted data

Q11. What skills or knowledge might be beneficial before taking this seminar?
A. Experience with web design
B. Completion of Access beginner level courses (Levels 1-3)
C. Familiarity with Photoshop
D. Experience in C++ programming

Q12. In advanced lessons, what additional topics are covered?
A. VBA programming and SQL
B. Data warehousing tools
C. Hardware installation
D. Mobile app development

Q13. What is the recommended way for a student to get the most benefit from this seminar?
A. Only download and use the sample database files
B. Watch without practicing
C. Build the databases yourself by following along with the videos
D. Focus only on the quizzes

Q14. What will you find if you are using Access 2010, 2007, or even 2003 or earlier with this seminar?
A. Major features will be missing in earlier versions
B. Relationship concepts will be the same, differences may only be in menu and ribbon interfaces
C. You cannot follow along unless you have Access 2010
D. Files are not compatible with earlier versions

Q15. For questions during the seminar, students are encouraged to:
A. Email the instructor directly
B. Post in the student forums associated with each lesson
C. Call customer service
D. Send messages via social media

Answers: 1-B; 2-D; 3-B; 4-C; 5-B; 6-C; 7-B; 8-B; 9-B; 10-B; 11-B; 12-A; 13-C; 14-B; 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 covers everything you need to know about Microsoft Access relationships. I am Richard Rost, and I am here to walk you through all the different ways to set up and use relationships within your Access databases.

The focus of this seminar is to help you fully understand the different types of relationships available in Microsoft Access. We will begin by seeing what happens when a database is set up without any relationships, and then move through the most common and useful relationship types you will encounter. These include one-to-many, one-to-one, and many-to-many relationships, among others.

Throughout our lessons, we will build eight different databases together, each designed to illustrate a particular relationship type. These databases can serve as templates to help you get started with your own projects. You are welcome to download sample files from my website, or you can get hands-on experience by building the databases alongside me as I work through the lessons. I always recommend constructing the databases yourself to reinforce your understanding, but if you are pressed for time, feel free to use my templates to give your project a boost.

I designed this seminar with a wide range of students in mind. The majority of the content is suitable for those at the beginner or intermediate level, especially if you have taken my introductory Access courses. If you are entirely new to Access, it is best to start with my Level 1, 2, and 3 beginner classes, which cover the basics like setting up a database, building tables, queries, and forms. Once you are comfortable with those concepts, you will find this relationships seminar much easier to follow.

Some lessons in this seminar go beyond the basics and are intended for advanced users. You will encounter some SQL work and a bit of VBA programming. I included these sections to keep my advanced students engaged, but if you are still learning, do not worry. You can take your time with these advanced parts or revisit them once you are ready.

At various points, I will reference other tutorials available on my website. Some of these are free and perfect for reviewing topics before we cover them in this seminar. I am using Access 2010 throughout these lessons. If you are using Access 2007, you will not face any difficulties. Nearly all of the relationship concepts we discuss remain unchanged in earlier versions like 2003, XP, or 2000. The primary differences will be in the menu layout and ribbon interface, not in database functionality.

If you have questions as you watch the lessons, you can post them in the student forums on my website. There is a forum connected to each video, and you will see it whether you are watching online or using my downloadable video player.

Let me break down exactly what we will cover in this seminar, one lesson at a time.

Lesson 1 offers a general overview of the different relationship types in Access and what you can expect from the rest of the seminar.

Lesson 2 demonstrates how a database might look if constructed improperly, without using any relationships. This is also referred to as a flat file database, where all information is stored in a single table instead of several related tables.

Lesson 3 moves on to creating a basic one-to-many relationship. Here, we separate parent information into its own table, showing how one student can have multiple parents linked to their record.

Lesson 4 advances this by putting together a form and subform system. You will see how, for each student, all related parents can appear directly within the student's form.

Lesson 5 polishes this setup by hiding unnecessary ID fields on the parent form and adding a combo box. With this, you can easily select the relationship type between the parent and student.

Lesson 6 focuses on building a query for mailing list purposes. This ensures that all parents who should receive notifications for a given student are included. We will look at global relationships, as well as how cascade deletes work so that removing a student from the database will automatically eliminate all associated parents.

Lesson 7 introduces one-to-one relationships. This type is useful when you want to store additional information only for some records, such as extra contact details. Storing this data separately can save space and offer some security benefits, which I will explain in this lesson.

Lesson 8 covers many-to-many relationships. You will learn how items in one list can be linked to multiple items in another list, and vice versa. For instance, products may come from several vendors, and vendors may supply several products. We will explore the structure and implementation of these relationships.

Lesson 9 continues our work with many-to-many relationships by showing you how to add subforms for both sides of the relationship. Each vendor will have a list of linked products, and each product will list its vendors, using combo boxes for selection.

Lesson 10 introduces you to self-joins, where a table can reference other records in itself.

Lesson 11 takes self-joins further by discussing many-to-many self-joins. This lets records, like people, be connected to many other records in the same table, which is perfect for handling things like family relationships.

Lesson 12 shifts the focus to reverse relationships. You will learn how selecting a relationship for one individual can automatically establish the reverse connection for the related individual.

Lesson 13 is about creating a button to save relationships in your tables. We will work with after-update events, use lookup functions, and write some basic SQL to make this work.

In the next lessons, we will begin bringing together techniques from previous sessions. I will show you how to build an organizational database that could serve for a company, family, or charitable group. The database will allow you to track both members and organizations and manage several different relationships at once, such as multiple addresses, phone numbers, and a full contact history. This will demonstrate how one entity can be involved in multiple one-to-many and many-to-many relationships, with everything managed from a single form.

Lesson 15 adds subforms for managing addresses and contact histories for both organizations and their members, making use of shared tables for these related details.

If you want a complete video tutorial that shows you every step needed to create and manage these relationships in Microsoft Access, visit my website at the link below. Live long and prosper, my friends.

Topic List

Overview of relationship types in Access

Problems with databases without relationships

Building a flat file database example

Creating a basic one-to-many relationship

Separating parent and student information into tables

Setting up forms with subforms for related data

Hiding unneeded IDs on forms

Adding a combo box to select relative type

Building a query for mailing list purposes

Implementing global relationships and cascade deletes

Setting up one-to-one relationships

Splitting data tables for space and security

Building a many-to-many relationship example

Using junction tables for many-to-many data

Creating subforms for many-to-many relationships

Adding combo boxes to subforms for selection

Implementing self-join relationships

Building many-to-many self-join relationships

Automating reverse relationships between records

Saving relationships to tables using VBA and SQL

Using after-update events for lookup functions

Building an organization database with complex relationships

Managing multiple one-to-many and many-to-many relationships

Adding subforms for addresses and contact history

Reusing tables for multiple record types