Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
Access Relationship Seminar
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   9 years ago

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!

Lesson 15 Upload Images   Link  
Kris Cunningham 
5 years ago
Hello. I am on Lesson 15 of this seminar and I think I finally (after several weeks of off and on studying/questioning) understand how I need to create my database. I do want to make sure I understand, though: I read that many to many relationships are just two one to many relationships joined by a junction table, but that isn t quite right, is it? For example, a one to many relationship would be A can have zero to many of B, but B could only have zero to one of A, correct? Like I read a street can have zero to many houses, but a house belongs to (well, not zero but) only one street. But a many to many says,  A can have zero to many of B AND B can have zero to many of A, correct? Like a room can be booked by zero to many guest, and a guest can book zero to many rooms. So, in that way, it s that quite the same, correct? Am I on the right track? Read More...
Add a Reply
Recurring Error Upload Images   Link  
Jack Tracy 
5 years ago
Thanks for this lesson, it is really helpful when it works.  I'm experience problems with it continuing to work, however.  

I've set up this code exactly as described and it works great.  Then I save, close and reopen the next day and attempting to run again I get bug errors.  I redo the entire process in a brand new set of forms, and again works great.  Next day, bug (error 459, pointing to the default input of the "personID" from the underlying form for the reverse relationship), which then leads to a bug in looking up the opposite ID as well.   Read More...
Add a Reply
DLookUp Upload Images   Link  
Richard W 
5 years ago
The information is already in a subform.  Rick said it is possible to put a subform in a subform but is a problem.  Further I was trying to learn DLookup since he talks about its power.  But if I have to use a query and a sub-subform, I guess I need to know where I can learn to do that!  Thanks for the answer.  I am trying to use the DLookup approach since I have, in fact, three groups of people with many to many relationships and I am  trying to standardize the way the information on all of them is stored . . . following Rick's advice that "if you have a second telephone number (or email or whatever) you should put it in a separate table" (paraphrased, not quoted). Read More...
Add a Reply

Show Older Comments...
View in Table Format

Start a NEW Conversation
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

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

12/3/2021Access Beginner 2
12/3/2021Access Beginner 2 Lessons
12/3/2021Access Beginner 2
11/30/2021Import Multiline Cells
11/30/2021Adam's Access Games
11/29/2021Prevent Close
11/22/2021Currency Symbols
11/13/2021Access Developer 36
11/13/2021Access Developer 36 Lessons

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Account Login
Online Theater
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access relationships  Page Tag: whatsnew  PermaLink