Exploring Microsoft Access

1
Exploring Microsoft Access

• Chapter 4
• Relational Databases, External Data, Charts, and
  the Switchboard
• By
• Robert T. Grauer
• Maryann Barber

2
Objectives (1 of 2)

• Describe one-to-many relationship
• Create a one-to-many relationship
• Use the Get External Data command
• Create and modify a multiple-table Select query

3
Objectives (2 of 2)

• Use aggregate functions to create a Totals query
• Use Microsoft Graph to create a chart based on a
  table or query
• Use the Switchboard Manager to create and/or
  modify a switchboard
• Compact, repair, and convert a database to a
  previous version of Access

4
Overview

• How to share data among Microsoft Office
  applications
• Display data from two tables in one query
• A Total query aggregates the results from groups
  of records to create summary information
• Create a user interface
• Compact, repair, and convert a database

5
Multiple-Table Queries

• One-to-many relationship
• Primary key
• Foreign key
• Referential integrity

6
Get External Data

• Get External Data command
• Export command
• Import Spreadsheet Wizard
• Import Text Wizard
• Importing versus linking

7
Multiple Table Query
Primary key in ConsultantsTable
Relationship between tables (one to many)
Foreign key in Clients Table
Each field table to display
8
Total Queries

• Summary functions
• Total row
• Group By
• Count function
• Sum function

9
Total Query
Click drop-downarrow
Select Count from drop-down menu
10
Chart Wizard (1 of 6)
Select Chart Wizard from the New Report dialog
box
Select the table or query for the report from
the drop-down menu
11
Chart Wizard (2 of 6)
Select the fields to be included in the chart
Inserts only the selected field
Inserts all fields
Removes only the selected field
Removes all fields
12
Chart Wizard (3 of 6)
Select the chart type
Description of the currently selected chart type
13
Chart Wizard (4 of 6)
Preview the chart
14
Chart Wizard (5 of 6)
Title the chart
Legend display
View the chart or design view
15
Chart Wizard (6 of 6)
Zoom
Modified Y and X axis now match the query data
Chart from Wizard
16
The User Interface

• Switchboard
• Switchboard Manager
• Switchboard Items table

17
Switchboard Manager
Graphic
Position of menu items
18
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

19
Compact the Database
Select the Database to compact
Click Compact
20
Chapter 4 Summary (1 of 2)

• 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

21
Chapter 4 Summary (2 of 2)

• Switchboard Manager creates the user interface
  (Switchboard)
• Convert Database command changes an Access 2000
  file to a previous version
• Compact and Repair Database command