Transcript Many-to-Many Relationships

Exploring Microsoft
Access 2003
Chapter 6
Many-to-Many Relationships:
A More Complex System
Objectives
• Define and explain a many-to-many relationship
• Use cascade Update and Cascade Delete
• Explain how AutoNumber field simplifies entering new
records (Autonumber is compatible with long integers)
• Create a main and subform based on a query
• Create a parameter query
• Use aggregate function
• Use Get External Data command
Overview
•
•
•
•
More database design practice
Extends relational database concepts
Use AutoNumber to simplify record creation
Extends subform and query information for
related tables
• Create a parameter query
• Create queries with built-in aggregate function
Case Study: Computer Super
Store
• A relational database with more than two tables
• Finding information becomes more difficult
unless you use a database
• Many-to-many relationship implemented by a
pair of one-to-many relationships
Case Study: Computer Super
Store
• Combined keys necessary when a pair of oneto-many relationships is built into a separate
table
• Using the AutoNumber field type keeps order
• Relationships window shows visual relationships
• Enforce referential integrity
Implementing Many-To-Many
Relationships
Enforce Referential
Integrity is selected
Many-to-many
Relationship is implemented
by a pair of one-to-man
relationships
Relationships and Referential
Integrity
Hands-On Exercise 1
•
•
•
•
•
Add a Customer
Create the Relationships
Delete an Order Details Record
Edit a Relationship
Delete a Record in the Orders Table
Subforms, Queries, and
AutoLookup
• Main and subforms based on queries:
– display information from multiple tables
– display records other than by primary key
• AutoLookup will find corresponding data
after unique ID is entered
Main Form and Subform
Main form has fields
from Consultants table
Subform has fields
from Client table
Designing a main and subform
Main form
detail
Subform
detail
Subforms and Multiple Table Queries
Hands-On Exercise 2
• Create the Subform Query
• Test the Query
• Create and Modify the Orders Form
• Change the Column Widths
• Enter a New Order
• Print the Completed Order
Advanced Queries
• Parameter query prompts for criteria when executed
• Total queries performs 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 performed on that
field for each group of records
Parameter Query
Run button
Table row indicates
Table from which
a field comes
Enter prompt is
square brackets
Advanced Queries
Hands-On Exercise 3
• Create the Query
• The Report Wizard
• Modify the Report Design
• Print the Report
• Copy an Existing Query
• Create and Run a Total Query
• Create a Parameter Query
• Exit Access
Expanding the Database
• Existing tables are unaffected by adding a fifth
table
• Use parameter query to calculate data
• Use Get External Data command to import a
table from another database
Adding Tables
Existing tables and
relationships are
unaffected by addition
of new tables
Expanding the Database
•
•
•
•
•
•
•
•
•
Hands-On Exercise 4
Import the Salesperson Table
Modify the Orders Table Design
Add the Salesperson to Existing Orders
Create a Relationship
Modify the order with Customer Information Query
Modify the Order Form
The Completed Order Form
Database Properties
Chapter 6 Summary (1 of 2)
• A many-to-many relationship requires an additional
table
• Many-to-many is implemented with a pair of one-tomany relationships
• Enforce referential integrity to prevent errors
• Base forms and subforms on queries
Chapter 6 Summary (2 of 2)
• 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
Practice with Access
1. Sales Commission
2. Find products that have never been ordered
3. Super Store Customer Form
4. Add Command Button to Order Form
5. Final Super Store Order Form
6. The Switchboard
7. The Startup Property
Case Studies
• Medical Research
• The Stock Broker
• The Video Store
• Class Scheduling
• Career Planning and Placement