Access Project 6

1
Access Project 6

• Switchboards, PivotTables, and PivotCharts

2
Objectives

• Create, add actions to, run, copy, and modify
  macros
• Create a switchboard and switchboard pages
• Modify switchboard pages
• Use a switchboard
• Import data and create a query

3
Objectives

• Create a PivotTable
• Change properties in a PivotTable
• Use a PivotTable
• Create a PivotChart and add a legend

4
Objectives

• Change the chart type and organization of a
  PivotChart
• Remove drop areas in a PivotChart
• Assign axis titles and a chart title in a
  PivotChart
• Use a PivotChart

5
Opening a Database

• Start Microsoft Office Access and open the Ashton
  James College database, which should be stored in
  the Data folder on your C drive

6
Creating a Macro

• Click the Macros object
• Click the New button
• Maximize the window by double-clicking its title
  bar

7
Adding Actions to a Macro

• Click the box arrow in the first row of the
  Action column to display a list of available
  actions
• Scroll down until Echo appears
• Click Echo
• Press the F6 key to move to the Action Arguments
  for the Echo action
• Click the Echo On box arrow

8
Adding Actions to a Macro

• Click No
• Press the F6 key to move back to Echo in the
  Action column, and then press the TAB key
• Type Turn echo off to avoid screen flicker in the
  Comment column, and then press the TAB key
• Select Hourglass as the action in the second row
• Press the TAB key and then type Turn on hourglass
  as the comment in the second row

9
Adding Actions to a Macro

• Press the TAB key and then select OpenForm as the
  third action
• Press the F6 key to move to the Action Arguments,
  and then click the Form Name box arrow
• Click Client Update Form, press the F6 key, press
  the TAB key, and then type Open Client Update
  Form as the comment
• Select Hourglass as the fourth action

10
Adding Actions to a Macro

• Change the Hourglas On argument to No, and then
  type Turn off hourglass as the comment
• Select Echo as the fifth action and then type
  Turn echo on as the comment
• Click the Close Window button to close the macro,
  click the Yes button to save the macro, type Open
  Client Update Form as the name of the macro
• Click the OK button

11
Adding Actions to a Macro
12
Running a Macro

• Right-click the Open Client Update Form macro and
  then click Run on the shortcut menu
• Click the Close Window button on the Client
  Update Form window title bar

13
Modifying a Macro

• Right-click the Open Client Update Form macro
• Click Design View
• If the Macro Name column appears, click the macro
  Names button on the Macro Design toolbar to
  remove the Macro Name column
• If the Condition column appears, click the
  Conditions button on the Macro Design toolbar to
  remove the Condition column

14
Modifying a Macro

• Click the row selector on the row containing the
  OpenForm action to select the row, and then press
  the INSERT key to insert a new row
• Click the Action column arrow on the new row,
  select Maximize as the action, and then type
  Maximize the window as the comment
• Click the Close Window button, and then click the
  Yes button to save the changes

15
Modifying a Macro
16
Copying and Pasting a Macro

• Ensure the Macros object is selected, and
  right-click the Open Client Update Form macro
• Click Copy to copy the macro to the clipboard
• Right-click any open area of the Database window
• Click Paste, type Open Trainer Master Form in the
  Macro Name text box in the Paste As dialog box
• Click the OK button

17
Copying and Pasting a Macro
18
Creating a Switchboard

• With the Database window appearing, click Tools
  on the menu bar, and point to Database Utilities
  on the Tools menu
• Click Switchboard Manager
• Click the Yes button to create a new switchboard

19
Creating Switchboard Pages

• Click the New button in the Switchboard Manager
  dialog box
• Type View Form as the name of the new switchboard
  page
• Click the OK button to create the View Form
  switchboard page
• Use the same technique to create the View Table,
  View Report, and Print Report switchboard pages

20
Creating Switchboard Pages
21
Modifying the Main Switchboard Page

• With the main Switchboard (Default) page
  selected, click the Edit button
• Click the New button, type View Form as the text,
  and then click the Switchboard box arrow
• Click View Form and then click the OK button to
  add the item to the switchboard
• Using the technique illustrated in the previous
  two steps, add the View Table, View Report, and
  Print Report items to the Main Switchboard page.
  In each case, the command is Go to Switchboard.
  The names of the switchboards are the sam as the
  name of the items. For example, the switchboard
  for the View Table item is called View Table

22
Modifying the Main Switchboard Page

• Click the New button, type Exit Application as
  the text, and click the Command box arrow
• Click Exit Application and then click the OK
  button to add the item to the switchboard
• Click the Close button in the Edit Switchboard
  Page dialog box to indicate you have finished
  editing the Main Switchboard page

23
Modifying the Main Switchboard Page
24
Opening a Switchboard

• Click the Forms object and then right-click
  Switchboard
• Click Open

25
Importing Data

• Create a new table, following the instructions on
  page AC 341
• With the Ashton James College database open,
  click File on the menu bar, point to Get External
  Data, and then click Import
• Click the Files of type box arrow in the Import
  dialog box and then click Text Files. Select the
  location of the files to be imported. Make sure
  the Course text file is selected. Click the
  Import button

26
Importing Data

• Make sure the Delimited option button is selected
  and click the Next button. Click First Row
  Contains Field Names check box and then click the
  Next button again
• Click the In an Existing Table option button and
  select the Course table from the list. Click the
  Next button, click the Finish button, and then
  click OK
• Repeat the previous four steps to import the
  Course Offerings text file

27
Creating a Query

• Follow the instructions at the bottom of page AC
  346 to relate the appropriate tables
• Click Tables on the Objects bar, and then click
  Trainer
• Click the New Object button arrow on the Database
  toolbar
• Click Query
• Be sure Design View is selected, and then click
  the OK button

28
Creating a Query

• Be sure the Query1 Select Query window is
  maximized
• Resize the upper and lower panes and the Trainer
  field list so all the fields in the Trainer table
  appear
• Right-click any open area in the upper pane,
  click Show Table on the shortcut menu, click the
  Client table, click the Add button click the
  Course Offerings table, click the Add button, and
  then click Close button in the Show Table dialog
  box
• Resize the Client and Course Offering field lists
  so all the fields appear
• Double-click the Trainer Number field from the
  Trainer table and the Client Number field from
  the Client table

29
Creating a Query

• Double-click the Course Number and Hours Spent
  fields from the Course Offerings table
• Right-click the Field row in the first open
  column
• Click Zoom on the shortcut menu, type Hours
  RemainingTotal Hours-Hours Spent in the Zoom
  dialog box

30
Creating a Query

• Click the OK button, click the Run button on the
  Query Design toolbar to ensure your results are
  correct, and then click the Close Window button
  for the window containing the query results
• Click the Yes button, type Trainers and Course
  Offerings as the name of the query, and then
  click the OK button

31
Creating a Query
32
Creating a PivotTable

• Click Queries on the Objects bar, right-click the
  Trainers and Course Offerings query, and then
  click Open on the shortcut menu
• Click the View button arrow
• Click PivotTable View
• If the PivotTable Field List does not appear,
  click the Field List button on the PivotTable
  toolbar to display the field list
• Click Course Number in the field list, and then
  ensure Row Area appears next to the Add to button

33
Creating a PivotTable

• Click the Add to button to add the Course Number
  field to the Row area
• Click the Trainer number field and then click the
  arrow to display the list of available areas
• Click Column Area and then click the Add to
  button to add the Trainer Number field to the
  Column area
• Click Hours Spent, click the arrow to display the
  list of available areas, click Data Area, and
  then click the Add to button to add the Hours
  Spent field to the Data area
• Use the same technique to add the Hours Remaining
  field to the Data area. Close the PivotTable
  Field List by clicking its Close button

34
Creating a PivotTable
35
Changing Properties in a PivotTable

• Right-click the Sum of Hours Spent box, and then
  click Properties on the shortcut menu
• Click the Captions tab in the property sheet
• Delete the current entry in the Caption property
  box, type Spent as the new value for the Caption
  property, and then close the property sheet
• Use the same technique to change the caption for
  the Sum of Hours Remaining box to Remaining

36
Changing Properties in a PivotTable
37
Using a PivotTable

• If necessary, click Queries on the Objects bar,
  right-click the Trainers and Course Offerings
  query, and then click Open on the shortcut menu
• Click the View button arrow and then click
  PivotTable View
• Click the plus sign () under trainer number 42
• Click the minus sign (-) under trainer number 42
  to again display data for trainer number 42
• Click the Trainer Number arrow

38
Using a PivotTable

• Click the Check box for trainer number 42 to
  remove the check mark, and then click the OK
  button
• Click the Trainer Number arrow, click the All
  check box to display all trainer numbers, and
  then click the OK button
• Click the Field List button to display the
  PivotTable Field List. Click Client Number,
  click the arrow to display a list of available
  areas, click Filter Area, and then click the Add
  to button to add the Client Number field to the
  Filter area
• Click the Client Number arrow

39
Using a PivotTable

• Click the check boxes in front of clients BS27
  and CP27 to remove the check marks, and then
  click the OK button
• Click the Client Number arrow, click the All
  check box, and then click the OK button to
  display data for all clients
• Drag the Trainer Number field from the Column
  area to the Row area, and then drag Course Number
  field from the Row area to the Column area
• Click the Close Window button for the window
  containing the PivotTable
• Click the No button when asked if you want to
  save your changes

40
Using a PivotTable
41
Creating a PivotChart and Adding a Legend

• If necessary, click Queries on the Objects bar,
  right-click the Trainers and Course Offerings
  query, and then click Open on the shortcut menu
• Click the View button arrow, and then click
  PivotChart View
• If the Chart Field List appears, close the field
  list by clicking its Close button
• Click the Show Legend button

42
Creating a PivotChart and Adding a Legend
43
Changing the Chart Type

• If the Chart Type button is dimmed, click the
  Chartspace
• Click the Chart Type button on the PivotChart
  toolbar, and then, if necessary, click the Type
  tab
• Click the 3D Stacked Column type, and then close
  the Properties window

44
Changing PivotChart Organization

• Click the By Row/By Column button on the
  PivotChart toolbar

45
Assigning Axis Titles

• Right-click the axis title to the left of the
  chart, and then click Properties on the shortcut
  menu
• Click the Format tab in the Properties window,
  and then click the Caption box
• Use the BACKSPACE or DELETE key to delete the old
  caption

46
Assigning Axis Titles

• Type Hours as the new caption
• Close the property sheet to complete the change
  of the axis title
• Use the same technique to change the other axis
  title to Trainer

47
Removing Drop Areas

• Click View on the menu bar
• Click Drop Areas on the View menu

48
Using a PivotChart

• Click Queries on the Objects bar, right-click the
  Trainers and Course Offerings query, and then
  click Open on the shortcut menu
• Click the View button arrow, and then click
  PivotChart View. Click View on the menu bar
• Click Drop Areas on the View menu, and then click
  the Trainer Number arrow
• Click the check box for trainer number 42 to
  remove the check mark, and then click the OK
  button

49
Using a PivotChart

• Click the Close Window button for the window
  containing the PivotChart
• Click the No button when asked if you want to
  save your changes

50
Summary

• Create, add actions to, run, copy, and modify
  macros
• Create a switchboard and switchboard pages
• Modify switchboard pages
• Use a switchboard
• Import data and create a query

51
Summary

• Create a PivotTable
• Change properties in a PivotTable
• Use a PivotTable
• Create a PivotChart and add a legend

52
Summary

• Change the chart type and organization of a
  PivotChart
• Remove drop areas in a PivotChart
• Assign axis titles and a chart title in a
  PivotChart
• Use a PivotChart

53
Access Project 6 Complete