Transcript Slide 1 - UMM Directory

Exploring Microsoft Access
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 Relationships window
• Define referential integrity
Objectives (2 of 2)
• Distinguish between a main form
and a subform
• Create a query based on multiple tables
• Create a main form with two subforms
Overview (1 of 2)
• The real power of Access: one-to-many
relationships between tables
• Primary key of one table is foreign key of
the related table
• Referential integrity ensures consistency
between tables
Overview (2 of 2)
• Main forms and subforms created from
related tables.
• Reports based on a multiple-table query
Relationships Window
CustomerID is primary key
in Customers table
Relationship line
Enforce Referential
Integrity
CustomerID is
foreign key in
Loans table
Referential Integrity (1 of 2)
• Access automatically implements a form of
data validation during data entry
• Can enter a record in the “one” table, but
not in the “many” table if record contains an
invalid value for the foreign key
• This ensures related tables are consistent
with one another.
Referential Integrity (2 of 2)
Delete Record button
Click + to display
related records
(subdatasheet)
Cannot delete a Customer without first deleting related Loan
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
Form Wizard (1 of 4)
Select all fields
to add to the form
Step 1
Select the table
or query for
the form
Step 2
Form Wizard (2 of 4)
Select the table
or query for
the subform
Step 3
Select all fields
to add to the subform
Form Wizard (3 of 4)
Choose how to display
the form and subform
Step 5
Step 4
Form Wizard (4 of 4)
Enter a name for the
form and the subform
Step 6
Choose the style
Step 7
Subform
Customer
C01
Loans for
Customer C01
Status bar for
Loans form
Status bar for Customers 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
Column selector
Select table
Enter criteria
Multiple Subforms (1 of 2)
• A main table can have multiple levels of
subforms
• Useful for displaying a one-to-many
relationship within a one-to-many
relationship
Multiple Subforms (2 of 2)
Customer C01
Loan L031 for
Customer C01
Payments for
Loan L031
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