Exploring Microsoft Access 2003

1
Exploring MicrosoftAccess 2003

• Chapter 6
• Many-to-Many Relationships
• A More Complex System

2
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

3
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

4
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

5
Case Study Computer Super Store

• Combined keys necessary when a pair of
  one-to-many relationships is built into a
  separate table
• Using the AutoNumber field type keeps order
• Relationships window shows visual relationships
• Enforce referential integrity

6
Implementing Many-To-Many Relationships
Enforce Referential Integrity is selected
Many-to-many Relationship is implemented by a
pair of one-to-man relationships
7
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

8
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

9
Main Form and Subform
Main form has fields from Consultants table
Subform has fields from Client table
10
Designing a main and subform
Main form detail
Subform detail
11
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

12
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

13
Parameter Query
Run button
Table row indicates Table from which a field comes
Enter prompt is square brackets
14
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

15
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

16
Adding Tables
Existing tables and relationships are unaffected
by addition of new tables
17
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

18
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-to-many relationships
• Enforce referential integrity to prevent errors
• Base forms and subforms on queries

19
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

20
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

21
Case Studies

• Medical Research
• The Stock Broker
• The Video Store
• Class Scheduling
• Career Planning and Placement