exploring microsoft access chapter 5 one-to-many relationships: subforms and multiple table queries
DESCRIPTION
One-to-many relationship essential in database design Use the Relationships window Define referential integrity Objectives (1 of 2)TRANSCRIPT
![Page 1: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/1.jpg)
![Page 2: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/2.jpg)
Exploring Microsoft Access
Chapter 5One-to-Many Relationships:
Subforms and Multiple Table Queries
![Page 3: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/3.jpg)
• One-to-many relationship essential in database design
• Use the Relationships window
• Define referential integrity
Objectives (1 of 2)
![Page 4: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/4.jpg)
• Distinguish between a main form and a subform
• Create a query based on multiple tables
• Create a main form with two subforms
Objectives (2 of 2)
![Page 5: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/5.jpg)
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
![Page 6: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/6.jpg)
• Main forms and subforms created from related tables.
• Reports based on a multiple-table query
Overview (2 of 2)
![Page 7: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/7.jpg)
Relationships WindowCustomerID is primary keyin Customers table
Relationship line
CustomerID is foreign key in Loans table
Enforce ReferentialIntegrity
![Page 8: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/8.jpg)
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.
![Page 9: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/9.jpg)
Referential Integrity (2 of 2)Delete Record button
Click + to displayrelated records(subdatasheet)
Cannot delete a Customer without first deleting related Loan
![Page 10: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/10.jpg)
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
![Page 11: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/11.jpg)
Form Wizard (1 of 4)
Select the tableor query forthe form
Select all fieldsto add to the form
Step 1
Step 2
![Page 12: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/12.jpg)
Form Wizard (2 of 4)
Step 3
Select the tableor query forthe subform
Select all fieldsto add to the subform
![Page 13: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/13.jpg)
Form Wizard (3 of 4)
Step 4
Step 5
Choose how to displaythe form and subform
![Page 14: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/14.jpg)
Form Wizard (4 of 4)
Choose the style
Enter a name for the form and the subform
Step 6
Step 7
![Page 15: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/15.jpg)
Subform
CustomerC01
Loans forCustomer C01
Status bar forLoans form Status bar for Customers form
![Page 16: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/16.jpg)
Multiple Table Queries
• Query information from two tables
• Create a select query using both tables
• Query window displays selected fields of each table
![Page 17: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/17.jpg)
Multiple Table Queries
Column selector
Enter criteria
Select table
![Page 18: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/18.jpg)
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
![Page 19: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/19.jpg)
Multiple Subforms (2 of 2)
Customer C01
Loan L031 forCustomer C01
Payments forLoan L031
![Page 20: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/20.jpg)
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
![Page 21: Exploring Microsoft Access Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries](https://reader034.vdocuments.us/reader034/viewer/2022051104/5a4d1b617f8b9ab0599ad5b5/html5/thumbnails/21.jpg)
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