Transcript 05_access_8

Many-to-Many Relationships: A
More Complex System(Wk8)
Exploring Office 2003 - Grauer and Barber
1
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
Exploring Office 2003 - Grauer and Barber
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
Exploring Office 2003 - Grauer and Barber
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
Exploring Office 2003 - Grauer and Barber
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
Exploring Office 2003 - Grauer and Barber
5
Implementing Many-To-Many
Relationships
Enforce Referential
Integrity is selected
Many-to-many
relationship is
Many-to-many
implemented
by
relationship
is
a
implemented
by a
pair
of one-topair of
one-to-many
many
relationships
relationships
Cascade Delete
Related Records is
selected
Exploring Office 2003 - Grauer and Barber
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
Exploring Office 2003 - Grauer and Barber
7
Cascaded Deletion

When a record in the primary table is deleted,
Access simultaneously deletes the
corresponding records in the related table
Exploring Office 2003 - Grauer and Barber
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
Exploring Office 2003 - Grauer and Barber
9
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)
Exploring Office 2003 - Grauer and Barber
10
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
Exploring Office 2003 - Grauer and Barber
11
Main Form and Subform
Main form has fields from
Orders and Customers
tables
Subform has fields
from Order Details
and
Products tables
Exploring Office 2003 - Grauer and Barber
12
Designing a main and a subform
Main form detail
Subform detail
Exploring Office 2003 - Grauer and Barber
13
Hands-on Exercise 2

Title of Exercise: Subforms and MultipleTable 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)
Exploring Office 2003 - Grauer and Barber
14
Designing a main and a subform
Main form detail
Subform detail
Exploring Office 2003 - Grauer and Barber
15
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
Exploring Office 2003 - Grauer and Barber
16
Parameter Query
Prompt is
displayed when
query is run
Prompt is entered in square Brackets
Exploring Office 2003 - Grauer and Barber
17
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


Exploring Office 2003 - Grauer and Barber
18
Total Queries
Records are grouped by
like values of OrderID
Arithmetic operation to be
performed on group
Exploring Office 2003 - Grauer and Barber
19
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)
Exploring Office 2003 - Grauer and Barber
20
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)
Exploring Office 2003 - Grauer and Barber
21
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
Exploring Office 2003 - Grauer and Barber
22
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
Exploring Office 2003 - Grauer and Barber
23
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
Exploring Office 2003 - Grauer and Barber
24
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
Bob’s Burgers
Exploring Office 2003 - Grauer and Barber
25
Questions?
Exploring Office 2003 - Grauer and Barber
26