Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  

News      User Comments     History     Notify Me

6/20/2012 1:34:26 AM
Access Relationship Seminar
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

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. This seminar will teach you about all of the following types of relationships:

1. No relationships
2. One-to-Many
3. One-to-One
4. Many-to-Many
5. Self-Join One-to-Many
6. Self-Join Many-to-Many
7. Reverse Relationships
8. Multiple Relationships

We will build sample databases to track:

1. Children to Parents with their relation (one-to-many)
2. Which parents receive mail on their child's behalf
3. Student data with a secondary optional detail table (one-to-one)
4. Vendors to Products and vice versa (many-to-many)
5. Employees to Supervisors (self-join, one-to-many)
6. Complete family history with relationships (self-join, many-to-many)
7. Organizations to Members (whether companies, families, charities, etc.)

You can learn more about this seminar here: Access Relationship Seminar

Of course if you have any questions, post them here!

Permanent Link
Course Link: Access Relationship Seminar
Keywords: access relationships
Page Tag: whatsnew
Post Reply

Relationships Comment from Kris Cunningham @ 10/31/2016
I need to make sure I understand, please: ~5.35 in Access Relationship Seminar Lesson 15 - So, you had both OrgID and MemberID fields in Address table b/c you knew ahead of time you were going to need them on *separate* occasions, e.g., the OrgID to be the Child Link in the AddressSubF to the OrgID in the OrgF and then the MemberID to the be Child Link in the AddressSubF to the MemberID in the MemberF for when you did an example of putting subforms into the MemberF, is that right?  B/c when you started adding those 2 ID fields to the Address table, I immediately thought you were making it a junction table in order to have a many to many relationship with the Org AND the Member at the same time, but you said at ~20:43 that you were setting up a one to many relationship between the AddressSubF and the OrgF and then the AddressSubF and the MemberF separately, right? Also, at first, I didn t see why you could not add an AddressID to the OrgXMemberJT (the junction table) and make the Address a many to many between the Org AND the Member b/c theoretically that could happen. In other words, I wanted to filter the addresses to just show those shared by both the Org AND the member, but when I tested it and added an Address combo box to the OrgXMemberSubF, next to the MemberID combo box, the values of the address did not match the members, only the organization. 1) I m getting so confused now, so why did the Address combo box make a relationship with the Org and not the Members in the combo box next to it? All of the ID fields, OrgID, MemberID, and AddressID are in the junction table, OrgXMemberJT. (I based the Address combo on the Address table, pulled over the AddressID and Street fields and stored the value in the AddressID.) 2) To filter like I m suggesting, would there need to be criteria in a query that would be the basis for both combo box fields, MemberID and AddressID? If so, would you just say in the Member ID criteria =AddressID and in the AddressID field criteria under the Or row put = MemberID? I don t think you would want to do this but just for argument s sake. Thank you, again.

Reply from Alex Hedley:

Maybe writing down the values you want to see on paper would be a good way to visualise, then transfer it to the db once you have a grasp.

Sometimes it's hard to look at a table of just numbers, 1 option is to create a Query that joins the Tables to their respective IDs then pull in a Name Field to see what you have.

From that you can take the row you would expect to see and see what filters you would need to apply.
Remember AND across OR down, it's likely you'd want AND across if you are wanting to filter for specific Orgs etc.

Show Just This Thread        Post Reply
Relationships Comment from Ramona Woitas @ 3/7/2015
Hi Richard! I am trying to get my head around this again. lol.

Relationship Seminar, Lesson 15, at 6:22 you said you have members which is a many to many relationship. I take this to mean that multiple members can belong to multiple organizations.  Right?

Then you said that addresses is a one to many relationship, from either an organization or a member.  This is where I am confused.  A member can have multiple addresses, and an org can have multiple addresses. But the address is only listed in the table once. And the address can only be used by either the member "OR" the organization. Is this right? Do I finally get it?

Reply from Alex Hedley:

Maybe this MS article with the following example may help:

One-to-many relationships

A one-to-many relationship is the most common kind of relationship. In this kind of relationship, a row in table A can have many matching rows in table B. But a row in table B can have only one matching row in table A. For example, the "Publishers" and "Titles" tables have a one-to-many relationship. That is, each publisher produces many titles. But each title comes from only one publisher.

Many-to-many relationships

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table that is called a junction table. The primary key of the junction table consists of the foreign keys from both table A and table B. For example, the "Authors" table and the "Titles" table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the "TitleAuthors" table.
Show Just This Thread        Post Reply
Relationships Comment from Christophe Smit @ 10/16/2014
I'm trying to set up the relationships for a procycling database on one hand and a soccer table on the other hand; as an excercice. In the procycling DB a rider can switch teams (team x season 2014, team y season 2015). Teams can exsist in season 2014 but not anymore in season 2015.
In soccer DB it is more complex as a player can change teams during the same season. I tried different ways but without result. How should my relationships look like in both DB's?

Reply from Alexander Hedley:

Hi Christophe,
If we convert this to the lessons Rich has taught you could have an Employee and a Company.
The Employee could have a CompanyID that is a Foreign Key to the CompanyT.
You could then have a combo that lists all the Companies and choose one for the Employee.
You could then change this if the Employee was to move Companies.

To show only certain Companies you could use a WHERE clause and filter it given some criteria.
Show Just This Thread        Post Reply
Reverse Relationships Comment from William B @ 8/28/2013
Can you do reverse relationships in a form and subform, as you have done in this seminar?  I was just wondering if it worked the same way, or if there would be some different SQL or VBA required for it to run correctly when that specific form is open?

Love the seminars by the way!

Reply from Richard Rost:

I don't see why not. It may be tricky though. A reverse relationship is really just a fancy one-to-one relationship, so you wouldn't need a subform.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 6+5:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



You may want to read these articles from the 599CD News:

8/15/2019Last Chance for Access CDs
8/1/2019Customer List Form Template
8/1/2019New Template Access Customer Database
7/30/2019Microsoft Access Developer 13
6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP