1 / 22

Exploring Microsoft Access 2003

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.

sonja
Download Presentation

Exploring Microsoft Access 2003

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exploring MicrosoftAccess 2003 Chapter 5 One-to-Many Relationships: Subforms and Multiple Table Queries

  2. 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

  3. Objectives (2 of 2) • Create a query based on multiple tables • Create a main form with two subforms

  4. Overview • One-to-many relationships between tables isthe real power of Access • Primary key of one table is foreign key of therelated table • Referential integrity ensures consistency between tables • Main forms and subforms created from relatedtables. • Reports based on a multiple-table query

  5. Case StudyConsumer 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

  6. Relationships Window ConsultantID is primary key in Consultant table Relationship line ConsultantID is foreign key in Clients table

  7. Referential Integrity Delete Record button Click + to display related records You cannot delete a Consultant without first deleting related Clients

  8. 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

  9. 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

  10. Subform Consultant C01 Client related to Consultant C01 Status bar for Clients form Status bar for Consultants form

  11. 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

  12. Multiple Table Queries • Query information from two tables • Create a select query using both tables • Query window displays selected fieldsof each table

  13. Multiple Table Queries Run button Column selector Select table Enter criteria

  14. 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

  15. Expanding the Database • Can expand a database without disturbing existing tables • Use relationship window to establishhow fields from specific tables will berelated • Can use multiple subforms

  16. 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

  17. Chapter 5 Summary (1 of 2) • Access database may contain multiple tables • Each table has a primary key which is theforeign key of a related table • Relationships window to create one-to-manyrelationships • Referential integrity ensures consistencybetween related tables

  18. 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 withoutdisturbing existing data

  19. Practice with Access (1 of 2) 1. Adding Clip Art 2. The Report Wizard 3. Employees by Location 4. Employees by Title

  20. Practice with Access (2 of 2) 5. The Switchboard 6. The Table Analyzer Wizard 7. Synchronizing Reports 8. Data Access Pages

  21. Case Studies • Recreational Sports League • The Personnel Director • The Franchise • Widgets of America

More Related