Training

1
Exploring Microsoft Access 2003
Chapter 6 Many-to-Many Relationships A More
Complex System Robert Grauer and Maryann Barber
Committed to Shaping the Next Generation
of IT Experts.
2
Objectives

• Use AutoNumber as the primary key
• Explain what field type can relate to an
  AutoNumber primary key field
• Implement a many-to-many relationship
• Use Cascade Update and Cascade Delete

3
Objectives (continued)

• Create a main and a subform based on a query
• Create a parameter query
• Use aggregate functions to perform calculations
  on groups of records
• Use Get External Data to add tables from another
  database

4
Case Study University Placement Center

• Students create a database that includes
• Required tables
• Many-to-many relationships
• A switchboard
• An About form
• Relationships diagram report

5
Many-to-Many Relationships

• A many-to-many relationship requires an
  additional table that has a one-to-many
  relationship to each of the related tables
• The primary key of the additional table is the
  combination of the primary keys of the related
  tables

6
Implementing Many-To-Many Relationships
Enforce Referential Integrity is selected
Many-to-many relationship is implemented by a
pair of one-to-many relationships

Cascade Delete Related Records is selected
7
Referential Integrity

• Ensures records in related tables are consistent
  with one another
• Prevents adding a record to a related table with
  an invalid foreign key
• Prevents deleting a record in the primary table
  when there are corresponding records in the
  related table

8
Cascaded Deletion

• When a record in the primary table is deleted,
  Access simultaneously deletes the corresponding
  records in the related table

9
Cascaded Updating

• When the primary key in the primary table is
  updated, Access automatically updates the value
  in the corresponding records in the related table

10
Hands-on Exercise 1

• Title of Exercise Relationships and Referential
  Integrity
• Objective to create relationships between
  existing tables to demonstrate referential
  integrity and allow cascaded deletion of related
  records
• Input file Computer Store
• Output file Computer Store (modified)

11
Subforms, Queries, and AutoLookup

• Main and subforms based on queries
• display information from multiple tables
• display records other than by primary key
• AutoLookup populates the corresponding fields
  once the primary key value is entered

12
Main Form and Subform

Subform has fields from Order Details and
Products tables
13
Designing a main and a subform
14
Hands-on Exercise 2

• Title of Exercise Subforms and Multiple-Table
  Queries
• Objective to use multiple-table queries to
  create a main form and related subform manually
  link the forms
• Input file Computer Store (from Exercise 2)
• Output file Computer Store (modified)

15
Designing a main and a subform
16
Parameter Queries

• Prompts the user for criteria when executed
• The prompt is enclosed in square brackets in the
  query design grid
• A parameter query may prompt for any number of
  variables

17
Parameter Query
Prompt is displayed when query is run
18
Total Queries

• Total Queries perform calculations on a group of
  records
• Total row Contains either Group by or aggregate
  entry
• Group By Records in the dynaset are to be
  grouped according to the like values
• Sum Function Specifies math to be performed on
  that field for each group of records

19
Total Queries
Records are grouped by like values of OrderID
Arithmetic operation to be performed on group
20
Hands-on Exercise 3

• Title of Exercise Advanced Queries
• Objective to copy an existing query create a
  parameter query create a total query using
  Aggregate Sum function
• Input file Computer Store (after Exercise 2)
• Output file Computer Store (modified)

21
Hands-on Exercise 4

• Title of Exercise Expanding the Database
• Objective to import a table from another
  database and modify the design
• Input files Sales Persons database
• Computer Store (after Exercise 3)
• Output file Computer Store (modified)

22
Summary

• A many-to-many relationship requires an
  additional table
• Many-to-many is implemented with a pair of
  one-to-many relationships
• The Enforce referential integrity option prevents
  errors
• Forms and subforms are based on queries

23
Summary (continued)

• The Parameter query uses prompts
• Aggregate functions perform calculations on
  groups of records
• New tables may be added at any time without
  affecting data in the existing tables

24
End-of-chapter Exercises

• Multiple Choice
• Practice With Access
• Exercise 1 Understanding Database Design
• Exercise 2 Unmatched Query Wizard
• Exercise 3 An Improved Order Form
• Exercise 4 Computer Store Switchboard
• Exercise 5 Return to National Bank
• Exercise 6 Medical Research Database Design
• Exercise 7 Medical Research Switchboard

25
End-of-chapter Exercises (continued)

• Practice With Access (continued)
• Exercise 8 National Conference Database Design
• Exercise 9 National Conference Switchboard
• Additional Mini Cases
• Health Clubs
• The Morning Paper
• The College Bookstore
• Bobs Burgers

26
Questions?