Transcript Access Project 6

Switchboards, PivotTables,
and PivotCharts – Project 6
Project 6 Overview
This project teaches us the complete switchboard system.
We will learn …
 to create and use macros.
 how to use the Switchboard Manager to create the
switchboard, switchboard pages, and switchboard items.
 how to use the Switchboard Manager to assign actions to
the buttons on the switchboard pages.
 to create a PivotTable and a PivotChart associated with a
query.
 how to customize various aspects of both the PivotTable
and PivotChart as well as how to use them.
2
Project 6 Perspective
AJC would like to add a switchboard system. This
would allow them to ...
 Increase user-friendliness and employee satisfaction
by allowing the click of a button to open
forms/tables/reports
 Summarize data in additional ways:
 In addition to using crosstab queries created previously,
they want to make changes to the way data is summarized
and presented on the screen:
 Presenting query results as PivotTables
AND
 Presenting results as PivotCharts provides a graphical format
3
Introduction (AC 314) - Switchboards
 Refer to Figures 6-1a and 6-1b, page AC 315
 Switchboard. - A form that includes buttons to perform
a variety of actions. When user clicks View Form 
Access displays View Form switchboard (6.1b). From
this switchboard, user can choose Client Update Form.
 The steps that have been saved are …




Clicking Forms on Objects bar
Right-clicking correct form
Click Open on shortcut menu
Double-click title bar to maximize window
4
Introduction (AC 314) - Macros




Clicking Forms on Objects bar
Right-clicking correct form
Click Open on shortcut menu
Double-click title bar to maximize window
Refer to Figures 6-1a and 6-1b, page AC 315
To perform these steps, Macros must be created.
 A Macro is a collection of actions designed to carry out
specific tasks, such as the list above.
 Macros run directly from the Database window.
 When a macro runs, Access executes the steps (actions).
 The switchboard system also uses macros. Clicking
certain buttons on the switchboard starts certain macros.
5
Introduction (AC 314) – PivotTable/PivotCharts
 Refer to Figures 6-1c and 6-1d, page AC 315
 A PivotTable (c) is similar to a Crosstab Query in that it
calculates a statistic (sum, avg, count) for data that are
grouped by two different types of information.
 The PivotTable differs from the Crosstab Query in that
the PivotTable is dynamic
 Once the PivotTable is created, you can easily …
 change the layout of the table by dragging items
 filter data by checking/unchecking values in drop-down lists
 A PivotChart (d) is a graphical analysis of the data
6
Creating and Using Macros (AC 317)
 We will now crate a macro, and actions to it.
 Many actions require additional information, called the
arguments of the action.
 Arguments appear in the lower portion of the Macro
window for that action, in which changes can be made.
 Another column in the Macro window is the Comment
column in which a brief description of the action purpose
is entered.
 Our first macro requires the below actions:
Don’t
need
to
use
on
fast
PCs
ARGUMENT
NEW VALUE
ACTION TO CHANGE FOR ARGUMENT
Echo
Echo On
No
Hourglass
OpenForm Form Name Client Update Form
Hourglass Hourglass On No
Echo
COMMENT
Turn echo off to avoid screen flicker
Turn on hourglass
Open Client Update Form
Turn off hourglass
Turn echo on
7
Modifying a Macro (AC 322)
Now that we have created a macro, we may need to modify it.
To do this …
 Select Design View on the shortcut menu to make changes
 You can insert actions by inserting a new blank row or delete
actions by deleting a row
 When modifying a macro, two additional columns may appear,
the Macro Name column and the Condition column:
 It is possible to group multiple macros into a single macro group.
When doing so, the Macro Name column is used to identify the
particular macro within the group
 It is also possible to have a macro be contingent on a certain
condition being true. If so, the condition is entered in the Condition
column.
8
Copying a Macro (AC 325)
 Now, I would like you to create the macros on page
AC 325, Table 6-2.
 Some of these macros are similar.
 You can copy a macro and modify to create another.
 Right-click the macro to be copied
 Click copy
 Right-click any open area of the database window
 Click Paste
 Type the New macro name in the “Paste As” box
 Open the new macro, modify it and save changes
9
Running Macros (AC 329)
 Now that the macros have been created, they will be
attached to switchboard items. One macro can run
another macro.
 Access has a macro level security feature.
 Various levels of macro security are available, for example:
high, medium, and low.
 If the security is medium or higher, Access displays a
Security Warning dialog box when a user attempts to open a
database containing macros.
 You should be very sure that a database comes from
a trusted source before opening a database that
contains macros.
10
Creating and Using a Switchboard (AC 329)
 An Application system is a collection of forms, reports, and
queries to meet the needs of a user or group of users. A
Switchboard system is one type of application system that is used
widely in the Windows environment.
 To create a switchboard, use Database Utilities  Switchboard
Manager
 Defining switchboard pages: Once the switchboard has been
created, switchboard pages can be created. Switchboard pages are
forms but they only can be opened from the main switchboard. The
Main Switchboard page is created automatically by Access.
 Modify switchboard pages. Commands must be associated with
each item on the switchboard page.
 Note that Access creates a form called Switchboard that you run to
use the switchboard. It also creates a table called Switchboard
Items. Do not modify or delete this table. Switchboard Manager
uses it to keep track of the various switchboard pages and items.
11
Adding Additional Tables (AC 339)
 You must now add 2 additional tables to the database:
(refer to Figures 6-36a&b and 6-37a&b, page AC 339-340)
 Course – contains data about specific courses offered by AJC
 Course Offerings – relates courses with clients taking the
courses at the current time.
 Once the data structure has been entered, we will add
data by
(1) saving text files to hard drive from website and
(2) importing files into corresponding tables
12
Adding Additional Tables (AC 339)
We will then set relationships for the tables:
 There is a one-to-many relationship between Trainer
and Client
 There is a many-to-many relationship between Client
and Course
 One client may take many courses and the same course is
offered to many clients.
 However, databases do not like many-to-many relationships, so
we must create a new table called an “associative entity” .
 Course Offerings becomes our “associative entity” table
 It relates Course to Client
 Client Number and Course Number make up the primary key
13
Pivot Tables (AC 343)
 An alternative to viewing data in Datasheet view or
Form view is PivotTable view
 It is an interactive, dynamic table that summarizes or
analyzes data
 Because the PivotTable is based on a query, we must
first create the query
 Once a query is built, you create the PivotTable by
opening the query and going into the PivotTable View.
 You can select the row, column, and data fields from
the PivotTable field list to structure your PivotTable.
 Properties can be changed within the Property Sheet
14
PivotCharts (AC 354)
Just as we created a PivotTable, we can create a PivotChart.
 We place fields in appropriate drop areas. These drop areas are
described below:
AREA
Series
PURPOSE
Data appear as data series, represented by colored markers
such as bars. Names and colors appear in the chart legend.
Category Data appear as categories or related groups of data. Labels
appear across the x-axis (horizontal) of the chart if the chart
has one.
Filter
Data appear in the chart but can be used to restrict the data
that appears.
Data
Data will be summarized within the chart.
15
PivotCharts (AC 354)
 Once your PivotChart has been created, you can …
 Change your chart type to one of numerous chart types
 Change the chart organization “By Row/By Column” –
switches x-axis and legend
 Assign axis titles
 Add a chart title and change its caption
16
Switchboards, PivotTables,
and PivotCharts – Project 6
Any Questions?