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)