Macros, Navigation Form, PivotTables, and Pivot ChartsAccess – Lesson 6

2

Project – Macros, Navigation Form, PivotTables and PivotCharts•Navigation Form ▫A form that includes buttons to view a variety of

reports and forms ONLY•Macros▫Collections of actions designed to carry out specific

tasks•Actions▫Execution of macros steps

•PivotTable▫Data summarizing tool

3

Macros, Navigation Form, PivotTable and PivotChart Design Guidelines

1. Determine when it would be beneficial to automate tasks in a macro

▫ Are there tasks involving multiple steps that would be more conveniently accomplished by running a macro than by carrying out all the individual steps?

2. Determine whether it is appropriate to create a Navigation Form

▫ Easy and convenient for users to view a variety of reports and forms just by clicking buttons

4

Macros, Navigation Form, PivotTable and PivotChart Design Guidelines

3. Determine the organization of the Navigation Form▫ Determine the various reports and forms that need to

be viewed by clicking buttons

4. Determine whether it is appropriate to present data as a PivotTable

▫ Do you need to calculate a statistic for data that is grouped by two different types of information?

5. Determine the organization of the PivotTable▫ Determine the fields for the rows, columns, data and

filters

5

Macros, Navigation Form, PivotTable and PivotChart Design Guidelines

6. Determine whether it is appropriate to present data as a PivotChart

▫ Do you want to summarize the same type of data as in a PivotTable, but graphically?

7. Determine the organization of the PivotChart▫ Determine to fields and the chart type that would best

display the data

6

Creating and Using Macros

•A macro consists of a series of actions that Access performs when the macros is run▫You will need to specify the actions when you create

the macro▫The actions are entered in a special window called a

Macro Builder window Many actions require additional information called the

arguments of the action

7

Single-Stepping a Macro

• If you have problems with a macro, you can single-step the macro

▫ Proceed through a macro a step at a time in Design view

8

To Create a Macros Group

• You can create a single file, called a macro group, and place the macros in the single macro group

• To place multiple macros in a macros group, you add the macros group action to the macro and then assign a name to each macro

9

Creating and Using a Navigation Form•A Navigation Form is a special type of form• It contains linked forms and reports for easy viewing•Buttons on the form can lead to other more

specialized forms or reports

10

Determine the Organization of the Navigation Form

1. Determine all of the tasks to be accomplished by clicking buttons in the Navigation Form

▫ What needs to be opened?

2. Determine any special requirements for the way the tasks are to be performed

11

PivotTables and Pivot Charts

PivotTable view presents data as a PivotTable An interactive table that summarizes or analyzes data

PivotChart view presents data as a PivotChart A graphical representative of the data▫ To create a PivotChart, you place fields in predefined areas of the

table called drop zones

12

Determine the Organization of the PivotTable1. Determine the field or fields that will be used for

the rows and columns2. Determine the field or fields that will be

summarized in the grid▫ Precisely what calculations is the PivotChart intended

to present?

3. Determine the field or fields that will be used to filer the data

13

Determine the Organization of the PivotChart

1. Determine the field or fields that will be used for the series

2. Determine the field or fields that will be used for the categories

3. Determine the field or fields that will be used for the data

4. Determine the field or fields that will be used to filter the data

5. Determine the type of chart