Transcript One-to-Many Relationships

Exploring Microsoft
Access 2003
Chapter 5
One-to-Many Relationships: Subforms
and Multiple Table Queries
Objectives (1 of 2)
• One-to-many relationship essential in database
design
• Use the Relationship window
• Define referential integrity
• Distinguish between a main form and a subform
Objectives (2 of 2)
• Create a query based on multiple tables
• Create a main form with two subforms
Overview
• One-to-many relationships between tables is the real
power of Access
• Primary key of one table is foreign key of the related
table
• Referential integrity ensures consistency between
tables
• Main forms and subforms created from related tables.
• Reports based on a multiple-table query
Case Study
Consumer Loans
• Understanding the need for a relational database
• One-to-many relationship – use Relationships
window
• Need a primary key, a unique identifier
• Foreign key in the ‘many’ table is the primary key
in the ‘one’ table
• Enforce referential integrity
Relationships Window
ConsultantID is primary
key in Consultant table
Relationship line
ConsultantID is foreign
key in Clients table
Referential Integrity
Delete Record button
Click + to display
related records
You cannot delete a Consultant without first deleting related Clients
One-to-Many Relationships
Hands-On Exercise 1
•
•
•
•
•
•
Open the National Bank Database
Create and Delete Relationships
Add a Customer Record
Add a Loan Record
Referential Integrity
Close the Database
Subforms
• Subform is a form within a form
• Main form would be based on primary table
while subform is based on related table
• Use Subform Wizard
• Can be created either on main form or dragged
from a separate form
Subform
Consultant C01
Client related to
Consultant C01
Status bar for
Clients form
Status bar for
Consultants form
Creating a Subform
Hands-On Exercise 2
•
•
•
•
•
•
•
Create Customers Form using Form Wizard
Modify Customers Form
Create Loans Subform
View Customers Form
Add Payment Amount
Enter a New Loan
Print the Form
Multiple Table Queries
• Query information from two tables
• Create a select query using both tables
• Query window displays selected fields
of each table
Multiple Table Queries
Run button
Column selector
Select table
Enter criteria
Queries and Reports
Hands-On Exercise 3
• Create a Select Query
• Move and Size the Field Lists
• Create and Run the Query
• Create and Print a Report
Expanding the Database
• Can expand a database without disturbing
existing tables
• Use relationship window to establish
how fields from specific tables will be related
• Can use multiple subforms
Linked Subforms
Hands-On Exercise 4
• Add a relationship
• Create the Payments Subform
• Change the Default View
• The Loans Subform in Form View
• The Customers Form
• The Finishing Touches
• Make Your Payments
Chapter 5 Summary (1 of 2)
• Access database may contain multiple tables
• Each table has a primary key which is the
foreign key of a related table
• Relationships window to create one-to-many
relationships
• Referential integrity ensures consistency
between related tables
Chapter 5 Summary (2 of 2)
• Subform is a form within a form
• A main form can have multiple subforms
• Select query can include fields from several tables
• Tables can be added at any time without
disturbing existing data
Practice with Access (1 of 2)
1. Adding Clip Art
2. The Report Wizard
3. Employees by Location
4. Employees by Title
Practice with Access (2 of 2)
5. The Switchboard
6. The Table Analyzer Wizard
7. Synchronizing Reports
8. Data Access Pages
Case Studies
• Recreational Sports League
• The Personnel Director
• The Franchise
• Widgets of America