Transcript ACCESS Chapter 4

Exploring Microsoft
Access 2003
Chapter 4- Proficiency:
Relational Databases, Pivot
Charts, and the Switchboard
Robert Grauer and Maryann Barber
Committed to Exploring
Shaping
the Next Generation of IT Experts.
Office 2003 - Grauer and Barber
1
Objectives

Import data from Excel into Access database

Create a one-to-many relationship

Create a report containing a relationships
diagram

Use the Get External Data command

Create and modify a multiple-table select query
Exploring Office 2003 - Grauer and Barber
2
Objectives (continued)

Create a totals query

Use Microsoft Graph to create a chart based
on a table or query

Create and modify a switchboard

Compact and repair a database
Exploring Office 2003 - Grauer and Barber
3
Get External Data

Get External Data command

Export command

Import Spreadsheet Wizard

Import Text Wizard

Importing versus linking
Exploring Office 2003 - Grauer and Barber
4
Demo On
Importing Data
From Excel
Exploring Office 2003 - Grauer and Barber
5
Creating Relationships
 Relational
Data Bases
 Creating Related Tables
FROM: Foreign key in Source
table
 TO: Primary Key in Target
table

Exploring Office 2003 - Grauer and Barber
6
Multiple-Table Queries

One-to-many relationship

Primary key

Foreign key

Referential integrity
Exploring Office 2003 - Grauer and Barber
7
Demo On Relating
Tables Using
UsedCars
Database
Exploring Office 2003 - Grauer and Barber
8
Demo On
Exporting Data
To Excel -- Used
Cars Database
Exploring Office 2003 - Grauer and Barber
9
Multiple Table Query
Foreign key in
Clients table
Primary key in
Consultants table
Relationship between
tables (one to many)
Each field and
table to display
Exploring Office 2003 - Grauer and Barber
10
Hands-on Exercise 1

Title of Exercise: Importing Data From Excel

Objective: to import an Access table from an
Excel workbook; create a one-to-many
relationship, and create a multiple-table query


Input file: Investment workbook (Excel)
Investment database (Access)
Output file: Investment workbook Investment
database (modified)
Exploring Office 2003 - Grauer and Barber
11
Total Queries

Summary functions

Total row

Group By

Count function

Sum function
Exploring Office 2003 - Grauer and Barber
12
Total Query
Click drop down
arrow in Total row
Select Count from
drop-down menu
Exploring Office 2003 - Grauer and Barber
13
Pivot Tables and Pivot Charts

Pivot tables compute summary stats for the
records according to supplied parameters

Pivot chart provides the same information in
graphical form
Exploring Office 2003 - Grauer and Barber
14
Pivot Tables
Exploring Office 2003 - Grauer and Barber
15
Pivot Chart
Exploring Office 2003 - Grauer and Barber
16
Chart Wizard (1 of 6)
Choose the Query
Exploring Office 2003 - Grauer and Barber
17
Chart Wizard (2 of 6)
Select the fields to be included in the chart
Inserts the
selected field
Removes only the
selected field
Inserts all fields
Removes all fields
Exploring Office 2003 - Grauer and Barber
18
Chart Wizard (3 of 6)
Select the chart type
Description of
The currently
Selected chart
type
Exploring Office 2003 - Grauer and Barber
19
Chart Wizard (4 of 6)
Preview the chart
Exploring Office 2003 - Grauer and Barber
20
Chart Wizard (5 of 6)
Title the chart
Legend display
option
Exploring Office 2003 - Grauer and Barber
21
Chart Wizard (6 of 6)
The completed chart
Exploring Office 2003 - Grauer and Barber
22
Hands-on Exercise 2

Title of Exercise: Total Queries, Charts, and
Pivot Tables

Objective: to create a total query; to use
Microsoft Graph to present data from an
Access object in graphical form


Input file: Investment database (after exercise 1)
Output file: Investment database (modified)
Exploring Office 2003 - Grauer and Barber
23
The User Interface

Switchboard

Switchboard Manager

Switchboard Items table
Exploring Office 2003 - Grauer and Barber
24
Switchboard Manager
Graphic
About
Investments form
Exploring Office 2003 - Grauer and Barber
25
Other Access Utilities

Convert Database command


Changes the file format to an earlier version of Access
Compact and Repair Database command

Compact: Reduces file size

Repair: Automatically run if unable to open an Access
database
Exploring Office 2003 - Grauer and Barber
26
Compact the Database
Select the
Database to
compact
Click Compact
Exploring Office 2003 - Grauer and Barber
27
Hands-on Exercise 3

Title of Exercise: The Switchboard Manager

Objective: to create a switchboard and user
interface; to compact a database.


Input file: Investment database (after exercise 2)
Output file: Investment database (modified)
Exploring Office 2003 - Grauer and Barber
28
Chapter 4 Summary

One-to-many relationships

A query can display data from multiple tables

The Get External Data command

A Total query performs calculations on a group
of records using summary functions
Exploring Office 2003 - Grauer and Barber
29
Chapter 4 Summary (continued)

Pivot table/chart displays summary stats for
records in a table according to supplied
parameters

Switchboard Manager creates the user
interface

Convert Database command changes an
Access 2000 file to a previous version
Exploring Office 2003 - Grauer and Barber
30
End-of-chapter Exercises

Multiple Choice

Practice With Access







Exercise 1 – The Oscars
Exercise 2 – Definitely Needlepoint
Exercise 3 – Metro Zoo
Exercise 4 – The Shopping Mall
Exercise 5 – Best Realty
Exercise 6 – The Fishing Pole
Exercise 7 – Database Wizard
Exploring Office 2003 - Grauer and Barber
31
End-of-chapter Exercises (continued)

Practice With Access (continued)


Exercise 8 – The HMO Database
On Your Own



Recreational Sports League
The Franchise
The Loan Officer
Exploring Office 2003 - Grauer and Barber
32
Questions?
Exploring Office 2003 - Grauer and Barber
33