Learn All About Microsoft Access Relationships
Welcome
In this seminar, you will learn just about everything there is to know about relationships in Microsoft Access. Setting up proper relationships between your tables is absolutely crucial to building a successful database. Bad table design, and bad relationships between those tables, is disasterous, and will cause you nothing but trouble down the line.
This seminar covers just about all of the different possible relationship scenarios that you will ever come across. We will build eight (8) different database projects so you can see how to set up good relationships between your tables.
Resources
Relationships Covered
- No relationships
- One-to-Many
- One-to-One
- Many-to-Many
- Self-Join One-to-Many
- Self-Join Many-to-Many
- Reverse Relationships
- Multiple Relationships
Databases Built
- Children to Parents with their relation (one-to-many)
- Which parents receive mail on their child's behalf
- Student data with a secondary optional detail table (one-to-one)
- Vendors to Products and vice versa (many-to-many)
- Employees to Supervisors (self-join, one-to-many)
- Complete family history with relationships (self-join, many-to-many)
- Organizations to Members (whether companies, families, charities, etc.)
Topics Covered
We will begin by looking at in table that has no relationships. You'll learn how NOT to build a database. This is basically one table with all kinds of information in it that shouldn't be there.

Next we'll build a database to track students and parents. This is a one-to-many relationship where one student can have many parents. We'll also keep track of which parents will receive mail for each student.

We will learn about one-to-one relationships. This is handy if you have a lot of data but you only have some information on most of your records. For example, if you have a student database but only have names for most of your students. You need to be able to track additional information such as address, phone number, and so on, however you only have that information for a small group of students. A 1-to-1 relationship saves a lot of space in your database in this instance. Plus you can also put sensitive information in a separate table, such as financial data.

Next we will learn how to create many-to-many relationships. These are quite popular but difficult to implement because they involve a third table. You need a junction table to link two other tables in a many-to-many relationship. For example ,we will learn how to track vendors and the products they sell, however each product may also be offered by multiple vendors. This is the basis for a many to many relationship.

Next we'll create a self-join table. This is where a table relates to itself. We'll create an employee form that allows us to choose a supervisor for each employee. However, the supervisor is just an employee, so the table relates to itself.

You'll also learn how to create a grouped report showing each supervisor in the employees he supervises. This requires building a query using the same table twice.

Next you'll create a another self-join table. We will track people and their relatives again, but this time we will set up a many-to-many relationship so each person can relate to each other person in the same table. This allows you to set up complete family trees (great for genealogy databases).

Next we will set up something that I like to call a reverse relationship. We'll specify two people, tell the database that one of them is (for example) the other person's uncle, and the database will figure out that the reverse relationship is niece or nephew.
This is one of the more advanced lessons in this seminar. It will use a little VBA programming and some SQL. I will show you everything you need to know to get the database working, but taking my SQL Seminar and advanced Access lessons first would be a great help. But don't worry, I'll show you how to get the database is working. Just follow along a step by step.

Finally will make a database to track organizations and members. Now in organization can be a family, company, charity, church, or anything else you want it to be. We'll track members of that organization in a many to many relationship. These can be family members, employees, donors, etc.
We will track multiple addresses for each organization and for each member. We will also build a contact management table to keep track of correspondence with each organization or member. We will build one big form so that we can see all of the information for each organization. We'll do the same thing for each member.

So as you can see we cover just about everything there is to know about relationships in Microsoft Access. From the very basic to the advanced, you will definitely know how to properly setup your tables and relationships after watching this seminar.. Of course, if you have any questions about whether or not this seminar is for you, please contact me.
NOTE: There is a NEW lesson on database table normalization found in Access Expert Level 2 that wasn't included in this seminar. It's not necessary for you to learn, but it covers a lot of theory that isn't discussed in this seminar. It was recorded after this seminar was finished.
Pre-Requisites
This is a Developer-Level Seminar. There will be a lot of VBA. It is strongly recommended that you have completed my entire Access Beginner and Expert series. My Developer 1 class is highly recommended so you understand the basics of programming in VBA. If not, at least watch my free Intro to VBA video. My SQL Seminar Part 1 is recommended, but not required.
Version
I am using Access 2010 in this seminar, however the lessons are perfectly valid for all versions of Access from 2003 and later. It's currently 2022 and I just recently verified that everything in this seminar still works with Access 2019 and Office 365.
Enroll Today
Enroll now so that you can watch these lessons, learn with us, post questions, and more.

Questions?
Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.
Keywords
microsoft access relationships, One-To-Many, One-To-One, Many-To-Many, Self-Join, flat file, child, parents, mailing, students, parents, school, global relationships, inner join, cascade delete, referential integrity, security, products, vendors, junction table, cross-reference table, employees, supervisors, report grouping, genealogy, family, dlookup, vba, sql, insert into, append query, organization, members, membership, charity, charities, churches, church, fund raising, donors, multiple addresses