For students:
Our latest online feature, CourseCasts, is a library
of weekly podcasts designed to keep your students up to date with the latest in
technology news. Direct your students to http://coursecasts.course.com, where
they can download the most recent CourseCast onto their mp3 player. Ken
Baldauf, host of CourseCasts, is a faculty member of the Florida State
University Computer Science Department where he is responsible for teaching
technology classes to thousands of FSU students each year. Ken is an expert in
the latest technology and sorts through and aggregates the most pertinent news
and information for CourseCasts so your students can spend their time enjoying
technology, rather than trying to figure it out. Open or close your lecture
with a discussion based on the latest CourseCast.
Table
of Contents
Students
will have mastered the material in Chapter Six when they can:
l Create and modify macros
and macro groups
l Run macros
l Create a switchboard and
switchboard pages
l Modify switchboard pages
l Use a switchboard
l Import data and create a
query
l Create a PivotTable
l Change properties in a
PivotTable
l Use a PivotTable
l Create a PivotChart and
add a legend
l Change the chart type
and organization of a PivotChart
l Remove drop zones in a
PivotChart
l Assign axis titles and a
chart title in a PivotChart
l Use a PivotChart
LECTURE NOTES
·
Point
out that the topics covered in this chapter are essential in a business
environment
CLASSROOM ACTIVITIES
1. Critical Thinking:
This chapter introduces a number of new topics. In a general sense, what do
each of the following terms mean to you: macro, switchboard, PivotTable, and
PivotForm?
AC 362: Project
Macros, Switchboards, PivotTables, and PivotCharts
LECTURE
NOTES
·
Define
switchboard
·
Define
macros and actions
·
Explain
the difference between a crosstab query and a PivotTable
·
Use
Figures 6-1a and 6-1b to illustrate a switchboard
·
Use
Figure 6-1c to illustrate a PivotTable
·
Use
Figure 6-1d to illustrate a PivotChart
·
Review
the tasks that will be covered in this chapter
o
Creating
a macro group with macros that will be used in the switchboard
o
Creating
a switchboard and adding switchboard pages
o
Adding
items and actions to the switchboard pages
o
Creating
the tables, relationships, and query used in creating a PivotTable and a
PivotChart
o
Creating
and using a PivotTable
o
Creating
and using a PivotChart
FIGURES
and TABLES: Figures 6-1a, 6-1b, 6-1c, 6-1d
TEACHER TIPS
The switchboard system that students create in
this chapter is one type of application system that has found widespread
acceptance in the Windows environment. An application system is simply an
easy-to-use collection of forms, reports, and queries designed to satisfy the
needs of some specific user or group of users, such as the users at JSP
Recruiters.
CLASSROOM
ACTIVITIES
1.
Class Discussion: Ask students for examples of other application systems they
have used. For example, if students register online, they are using an
application system.
2. Quick Quiz:
1)
What
is a switchboard? (Answer: A form that includes buttons to perform a variety of
actions)
2)
What
are macros? (Answer: Collections of actions designed to carry out some specific
task)
AC 364: Plan Ahead
(Critical Thinking): Macro, Switchboard, PivotTable, and PivotChart Design
Guidelines
·
Determine
when it would be beneficial to automate tasks in a macro
·
Determine
whether it is appropriate to create a switchboard
·
Determine
the organization of the switchboard
·
Determine
whether it is appropriate to present data as a PivotTable
·
Determine
the organization of the PivotTable
·
Determine
whether it is appropriate to present data as a PivotChart
·
Determine
the organization of the PivotChart
TEACHER TIPS
These guidelines are intended to help students
with their critical thinking skills. Students should understand the reasons for
creating macros, switchboards, PivotTables, and PivotCharts. Spend a
significant amount of class time reviewing each of these guidelines and use
example databases, such as a student database or a database of credit card
information to which students can relate. These points are emphasized at
appropriate locations in the chapter. Also, Cases and Places 3, 4, and 5
challenge students to apply these guidelines to creating macros, switchboards,
PivotTables, and PivotCharts for a database.
LECTURE
NOTES
·
For
figures that match those in the book, change screen resolution to 1024 x 768
·
Review
the steps to start Access
·
Review
the steps to open a database
TEACHER TIPS
You can use the Lecture Success
System for Access in conjunction with the Figures in the Book. To do this,
start Access and open the database from the appropriate folder. Then, start
your slide show containing the figures for the project. You can switch back and
forth between the slide show and Access by using the alt+tab key combination. You can use the Figures in the Book
to show the steps students should follow. If students need additional
reinforcement or ask questions about the task, you can switch to Access to do a
live demonstration.
AC 366: Creating and
Using Macros
LECTURE
NOTES
·
Discuss
the use of macros to perform tasks that typically require the user to click a
series of buttons or commands
·
Use
Figure 6-2 to illustrate beginning creating a macro
·
Use
Figure 6-3 to describe the Macro Builder window
·
Define
arguments
·
Use
Table 6-1 to review with students the macro they will create and explain the
macro actions
·
Use
Figures 6-4 through 6-9 to illustrate adding actions to a macro
·
Use
Figures 6-10 and 6-11 to describe how to single-step through a macro
·
Review
the steps to run a macro
·
Point
out in Figure 6-12 the result of running a macro to open a table in read-only
mode
·
Use
Figures 6-13 and 6-14 to illustrate modifying a macro
·
Use
Figure 6-15 to describe running the modified macro
·
Review
the steps to reverse the macro action
·
Point
out in Figure 6-16 the result of reversing the macro action
·
Use
Figures 6-17 and 6-18 to explain what happens when a macro contains errors
·
Use
Table 6-2 to review the additional macros that students will create
·
Define
macro group and use Figures 6-19 and 6-20 to illustrate creating a macro group
·
Use
Figure 6-21 to illustrate saving the macro group
·
Use
Figure 6-22 to describe adding the remaining macros to the macro group
·
Review
the procedures you should follow when you open a database that contains macros
FIGURES
and TABLES: Figures 6-2, 6-3, 6-4, 6-5, 6-6, 6-7, 6-8, 6-9, 6-10, 6-11, 6-12,
6-13, 6-14, 6-15, 6-16, 6-17, 6-18, 6-19, 6-20, 6-21, 6-22; Tables 6-1, 6-2
TEACHER TIPS
The way you create
macros in Access differs from the way you create macros in either Excel or
Word. For example, in Excel you can record a macro as you perform an action.
Students do not need to memorize macro actions.
The list box in the Macro window displays the actions in alphabetical order.
Students can use Access Help to find out more about macro actions.
CLASSROOM ACTIVITIES
1. Class Discussion: Ask students if they have
created and used macros in other software packages. How did they create those
macros?
1. Have students create
a macro in Excel and compare the procedure for creating a macro in Excel with
the procedure in Access.
AC 379: Creating and Using a
Switchboard
LECTURE
NOTES
·
Use
Figures 6-23 and 6-24 to illustrate creating a switchboard
·
Define
switchboard pages and use Table 6-3 to review the switchboard pages and the
purpose of each page and item
·
Use
Figures 6-25 and 6-26 to illustrate creating switchboard pages
·
Use
Figures 6-27 through 6-29 to illustrate modifying the main switchboard page
·
Use
Figures 6-30 through 6-33 to describe modifying the other switchboard pages
·
Use
Figures 6-34 and 6-35 to illustrate opening a switchboard
·
Point
out the Switchboard Items table in Figure 6-34; students should not modify this
table
·
Discuss
the ways to use a switchboard
·
Review
the step to close the switchboard and close the database
FIGURES
and TABLES: Figures 6-23, 6-24, 6-25, 6-26, 6-27, 6-28, 6-29, 6-30, 6-31,
6-32, 6-33, 6-34, 6-35; Table 6-3
BOXES:
1.
BTW: Displaying a Switchboard.
Discuss how to have the switchboard appear automatically when a database is
opened.
TEACHER TIPS
Students should understand that a switchboard is
a special kind of form. Switchboard pages also are forms but they only can be
opened from the main switchboard.
If students make a mistake when they create
their switchboard, they should use the Switchboard Manager button on the
Database Tools tab. Emphasize that students should not modify or delete
the Switchboard Items table. If students want to add a logo, change the
background color of the switchboard or make other formatting changes, they can
open the switchboard in Design view to make those changes.
CLASSROOM ACTIVITIES
1.
Class Discussion: Ask students how they would design a switchboard for the JSP
Recruiters database if they had to do it from scratch.
2.
Critical Thinking: What advantages are there to using a switchboard rather than
the Navigation Pane to select tables, forms, and reports? Are there any
disadvantages?
LAB
ACTIVITIES
1.
Have students open the switchboard in Design view and add a picture and the
current date.
AC 380: Plan Ahead (Critical Thinking);
Determine the Organization of the Switchboard
·
Determine
all the tasks to be accomplished by clicking buttons in the switchboard
·
Determine
any special requirements for the way the tasks are to be performed
·
Determine
how to group the various tasks
LECTURE
NOTES
·
Use
Figures 6-36a and 6-36b to discuss the structure and data for the Seminar table
·
Use
Figures 6-37a and 6-37b to discuss the structure and data for the Seminar
Offerings table
·
Review
the steps to open a database
·
Use
Figures 6-38 and 6-39 to illustrate creating the new tables
·
Review
the steps to import the data
·
Review
the steps to relate several tables
·
Use
Figure 6-40 to discuss the many-to-many relationship between clients and
seminars
FIGURES
and TABLES: Figures 6-36a, 6-36b, 6-37a, 6-37b, 6-38, 6-39, 6-40
BOXES:
1.
BTW: AutoNumber Field as Primary Key.
Review the purpose of the AutoNumber field.
2.
BTW: Copy the Structure of a Table.
Explain how to copy the structure of a table.
3.
BTW: Modify Composite Primary Keys.
Discuss why you would need to modify the primary key.
4.
BTW: Many-to-Many Relationships.
Explain how many-to-many relationships are implemented in Microsoft Access.
TEACHER TIPS
In Chapter 1, students created tables using
Datasheet view. In this chapter, they use Design view. Remind students that to
add a table to a database requires two steps. The first step is to design and
save the table first, and then enter data.
If students get a Microsoft Access dialog box
indicating that there were errors in importing the data, they should cancel the
process and review the structure of each table. The most common mistake that
students make is to assign the primary key incorrectly to a table where the
primary key is the combination of two or more fields.
There is a one-to-many relationship between
recruiters and clients. There is a many-to-many relationship between clients
and seminars. One client can have many seminars and the same seminar can be
offered to many clients. The Seminar Offerings table is an associative entity
that relates seminars and clients. While the combination of client number and
seminar number is the primary key, client number and seminar number are also
foreign keys. These foreign keys match the primary key of the Client and
Seminar table respectively.
CLASSROOM ACTIVITIES
1.
Class Discussion: Ask students for examples of other tables JSP Recruiters
might need.
AC 393: PivotTables and
PivotCharts
LECTURE
NOTES
·
Define
PivotTable and PivotChart
·
Emphasize
that both PivotTables and PivotCharts are dynamic
·
Use
Figures 6-41 through 6-44 to describe creating the query
·
Point
out the expression for the computed field on Figure 6-43
·
Use
Figure 6-45 to explain the layout of a PivotTable
·
Define
drop zones and use Table 6-4 to discuss the PivotTable drop zones
·
Use
Figures 6-46 through 6-49 to illustrate creating a PivotTable
·
Use
Figure 6-50 to describe changing properties in a PivotTable
·
Review
the step to save the PivotTable changes
·
Use
Figures 6-51 through 6-57 to illustrate using a PivotTable
·
Use
Figure 6-58 to describe the PivotChart window
·
Review
the PivotChart drop zones in Table 6-5
·
Use
Figures 6-59 and 6-60 to illustrate creating a PivotChart and adding a legend
·
Use
Figure 6-61 through 6-63 to describe changing the chart type
·
Use
Figure 6-64 to describe changing the PivotChart orientation
·
Use
Figure 6-65 to illustrate assigning axis titles
·
Use
Figure 6-66 to describe removing drop zones
·
Use
Figures 6-67 though 6-69 to illustrate adding a chart title
·
Review
the step to save the PivotChart changes
·
Use
Figures 6-70 through 6-72 to illustrate using a PivotChart
·
Review
the step to quit Access
FIGURES
and TABLES: Figures 6-41, 6-42, 6-43, 6-44, 6-45, 6-46, 6-47, 6-48, 6-49,
6-50, 6-51, 6-52, 6-53, 6-54, 6-55, 6-56, 6-57, 6-58, 6-59, 6-60, 6-61, 6-62,
6-63, 6-64, 6-65, 6-66, 6-67, 6-68, 6-69, 6-70, 6-71, 6-72; Tables 6-4, 6-5
BOXES:
1.
BTW: PivotTable Forms. Describe how
to create a PivotTable form.
2.
BTW: Certification. For more
information on the MCAS program see Appendix G or visit the Access 2007
Certification Web page.
3.
BTW: Quick Reference. Point out the location
of the Quick Reference Summary and the Access 2007 Quick Reference Web page.
4.
Other Ways: Encourage your students to explore other ways to create a
PivotTable.
5.
Other Ways: Encourage your students to explore other ways to create a
PivotChart.
6.
Other Ways: Encourage your students to explore other ways to assign axis titles.
TEACHER TIPS
PivotTables also are associated with Excel. Many
businesses use PivotTables to analyze past performance and predict future
trends. Because they are interactive, you can filter the data or drill down
to provide a more specific view of the data.
PivotTables and PivotCharts are not separate
objects in the database. They are different views of the query or table on
which they are based.
To change the orientation of a chart, use the
Switch Rows/Columns command on the Ribbon.
CLASSROOM
ACTIVITIES
1.
Critical Thinking: A PivotTable is an interactive table that summarizes or
analyzes data. A PivotChart is a graphical representation of the data. When
would you use PivotTables and PivotCharts? Why?
LAB
ACTIVITIES
1.
Have students create a crosstab query that is similar to the PivotTable. Ask
them to comment on the differences between the two.
AC 396: Plan
Ahead (Critical Thinking): Determine the Organization of the PivotTable
·
Determine
the field or fields that will be used for the rows and columns
·
Determine
the field or fields that will be summarized in the grid
·
Determine
the field or fields that will be used to filter the data
AC 404: Plan
Ahead (Critical Thinking): Determine the Organization of the PivotChart
·
Determine
the field or fields that will be used for the series
·
Determine
the field or fields that will be used for the categories
·
Determine
the field or fields that will be used for the data
·
Determine
the field or fields that will be used to filter the data
·
Determine
the type of chart
§
Learn
It Online is a series of online student exercises that test your knowledge of
chapter content and key terms.
§
Apply
Your Knowledge is a student assignment that helps you to reinforce the skills
and apply the concepts you learned in this chapter.
§
Extend
Your Knowledge is a student assignment that challenges you to extend the skills
you learned in this chapter and to experiment with new skills. You may need to
use Help to complete the assignment.
§
Make
It Right is a student assignment that requires you to analyze a presentation
and correct all errors and/or improve the design.
§
In
the Lab (Lab): In the Lab is a series of student assignments that ask you to
design and/or create a presentation using the guidelines, concepts, and skills
presented in this chapter. The assignments are listed in order of increasing
difficulty.
§
Cases
and Places is a series of student assignments where you apply your creative thinking
and problem solving skills to design and implement a solution.
·
actions
(AC 362)
·
arguments
(AC 367)
·
drop
zones (Ac 396)
·
macro
group (AC 377)
·
macros
(AC 362)
·
PivotChart
(AC 393)
·
PivotTable
(AC 393)
·
PivotTable
view (AC 393)
·
single-step
macro (AC 371)
·
switchboard
(AC 362)
·
switchboard
pages (AC 381)