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