Chapter 6 Many to Many Relationships

1
Chapter 6 Many to Many Relationships

• 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

2
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

3
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

4
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

5
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
6
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

7
Cascaded Deletion

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

8
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

9
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

10
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

11
Parameter Query
Prompt is displayed when query is run
12
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

13
Total Queries
Records are grouped by like values of OrderID
Arithmetic operation to be performed on group
14
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

15
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