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  
 
 
 

Access Relationship Seminar
Just about everything there is to know about relationships in Access

 

 

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.

Watch this video to learn more about this seminar:

 

Want to Learn More?

You can watch first two lessons plus the final review lesson in our Online Theater. There you will learn more about exactly what's covered. Then continue reading the course outline below if you have further questions. Of course, if you have specific questions about the seminar that aren't covered here, always feel free to contact customer service.

 

 
Seminars - Access Relationships
Description: Learn about the different ways to properly set up relationships in your Access databases.
Versions: I will use Access 2010, however the lessons are valid for all versions of Access back to 2000 with some minor cosmetic differences.
Pre-Requisites: This course is mostly for beginner to intermediate users. Basic knowledge of Access table, query, and form design is a must. You should have at least taken my Beginner Access courses before taking this seminar. A couple of the later lessons are for advanced users. There will be some VBA and a little bit of SQL used, so it would help for you to have taken my Access 300-level courses as well, but that's not necessary. I will explain what you need to know to get the databases working. Most of the course doesn't require any advanced knowledge, however.
Running Time: 3 Hours, 58 minutes
Cost: $99 - Order multiple courses to receive a discount up to 50% off

 

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.)
 

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.
 

 

Access Relationship Seminar - Outline

00. Intro (7:59)

01. Types of Relationships (13:04)
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

02. No Relationships (5:26)
Flat File
The wrong way to build a database
Child and Parents in Same Table

03. One-To-Many Relationships (10:45)
Delete parent data from student table
Create a RelativeTypeT
Create ParentT
Fill in sample data

04. One-To-Many Form 1 (12:38)
Create ParentF Subform
Set up relationship with subform wizard
Turn off navigation buttons
Turn off scroll bars

05. One-To-Many Form 2 (9:53)
Hide IDs
Combo Box for RelativeType
Combo Box Wizard

06. One-To-Many Query (15:34)
StudentQ for Mailings
Query Criteria
Field Name Aliases
OR Conditions
Show ALL students and related parents
LEFT Inner Join
Global Relationships
Cascade Delete

07. One-To-One Relationships (18:33)
Student
Extra Student Info
One-To-One Global Relationship
Command Button to Open 2nd Form
Show Specific Records
Forms!FormName!FieldName
Refer to Value on Another Form
Default Value
One-To-One Subform
Discuss Securing Data with Split DB
Discuss Multiple Back-End Files
One for Users, One for Admins

08. Many-To-Many 1 (11:47)
Products and Vendors
Multiple products per vendor
Multiple vendors per product
Junction table
Cross Reference Table
Vendor Form
Product Form

09. Many-To-Many 2 (14:24)
Products with Vendor Subform
Vendors with Product Subform
ProductVendorF
Product Combo Box
Vendor Combo Box
On Double Click Event
VBA Code to open a form
OnDblClick
Docmd.OpenForm
Where Criteria

10. Self Join One-To-Many (17:00)
Relating a Table to Itself
EmployeeT
SupervisorID
Supervisor Combo Box
String Concatenation
Calculated Field
Employee With Supervisor Q
Grouped Report
Sorting & Grouping Level
Report Showing Supervisor with Employees

11. Self Join Many-To-Many (20:46)
People to People with Relationships
Person table and form
Junction Table
PersonQ with Full Name
Form Subform for People and Relatives
Great for Genealogy Tracking

12. Reverse Relationships 1 (14:23)
Add opposite relationships
If Mother is picked, Child is default
Non-gender specific opposites
AllowAdditions
Do not allow additions to subform
Unbound forms
Control Source
Unbound Combo Boxes
Turn off Navigation Buttons
Record Selectors
Scroll Bars
Modal
Popup
Docmd.Openform
Set value across forms
Forms!FormName!FieldName

13. Reverse Relationships 2 (22:44)
AfterUpdate Event
DLOOKUP Function to find Opposite
SQL
INSERT INTO
Append Query
IsNull
MsgBox
Exit Sub
Docmd.Close
Refresh
DoCmd.SetWarnings

14. Multiple Relationships 1 (14:58)
Main Organization Table
Members Subtable
Great for Charities, Churches
Fund Raising possibilities
Good to track Family Data
Multiple subforms of data
One master form
OrgT for organization
Junction Table
Member Subform

15. Multiple Relationships 2 (21:56)
Address subform on Org Form
Address is for Org OR Member
Address subform on Member Form
OnDblClick to jump from Org to Member
Vice Versa
Contact History Subform
Contacts for both Orgs and Members

16. Review (5:43)
 

 


 

 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
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