Slicing and Dicing Data with Pivot Tables in Access and Excel

1
Slicing and Dicing Data with Pivot Tables in
Access and Excel

• Kathryn Broad
• Oklahoma City University Law Library
• South Central Voyagers Users Group 2007

2
(No Transcript)
3
PivotTables

• I am not an expert
• It looks hard, but is very easy
• The PivotTables set-up is wizard driven in Access
  and Excel 2003 and though it really doesnt seem
  like it in Access and Excel 2007, too

4

• To use with Voyager, you need the ODBC drivers
  installed, or someone else to export and send you
  the information from Voyager as an Excel
  spreadsheet
• You can also use pivot tables with any spread
  sheet or data base that you have built or
  imported into Excel
• Pivot Tables and Pivot Charts are available to
  use in both Access and Excel

5
Pivot Tables, or, The Big Picture

• The best use of Pivot Tables is for things that
  can be or need to be summarized, sub-totaled,
  totaled, grand totaled, and/or crossfoot totaled.
  They can also be used to count (like the OPAC
  search logs). Any data that appears as a series
  of rows organized under a set of common column
  headings is a candidate for PivotTables

6
HELP!!!! How do I do this???????

• When in doubt about the directions for using
  PivotTables or PivotCharts, use the Microsoft
  Help Files

7
What is a PivotTable? And what are PivotTables
used for?

• A way to display complex data in different ways
• Used to summarize information from large or
  lengthy queries using complex criteria
• A Pivot Table can answer questions on totals
• A Pivot Table can be created in Access using the
  Wizard, but to make changes you use Excel either
  from within Access or in the Excel window.

8
Gather the information

• Because PivotTables offer a great deal of
  flexibility, the underlying query or spreadsheet
  can contain more information than you think is
  wanted. The information can be filtered easily
  later. The fields you choose depend on what
  questions you want answered.
• Though Pivot Tables are available in Access, they
  actually work within Excel (you must have Excel
  loaded on your computer)

9

• PivotTables can take any list in Excel and let
  you change the look of the data without changing
  the original structure of the data
• Think of the questions youd like to ask about
  your data if other questions come to mind,
  rearrange the data to answer the new questions.

10

• It would take forever to go through a large data
  set. PivotTables allow you to summarize this
  data, and if necessary, rearrange the display

11

• Notice No data was harmed in the making of this
  production (you cant mess your original data
  up)

12
PivotTable lists within Access (2003)

• Create your query. Then click on View ?
  PivotTable view. Your query will run again

13
PivotTable lists in Access (2003)

• The PivotTable View opens. You can drag and drop
  to the form, or highlight a line, then tell
  Access to add it to the row area, column area, or
  data area

14
PivotTable lists within Access (2003)
15
PivotTable lists within Access (2003)

• A PivotTable list in Access is like a Print
  Preview for a report you cant manipulate the
  way it looks

16
PivotTable lists in Access (2007)

• In Access 2007 the PivotTable function is located
  on the Create tab, then More Forms dropdown

17
Access 2007 PivotTable list screen

• Method of creating a PivotTable list in Access
  2007 is the same as in Access 2003

18
Creating PivotTables in Excel

• PivotTables created in Access are actually
  PivotTable lists and dont offer all of the
  features available in Excel
• Opening an Access PivotTable list worksheet in
  Excel allow more for manipulations and formatting

19
Results from an Access query (over 20,000 lines)
transferred to Excel
20
Create a Pivot Table (in Excel 2007)
21
Choose the fields to display in your PivotTable
Drop Page Fields Here
Drop Column Fields Here
Place non-numeric field(s) here to show what
fields will be listed horizontally (in the
columns)
Drop Row Fields Here
Drop Data Items Here
Place a numeric field here to aggregate (usually
SUM) the data according to the other areas
Place a non-numeric field here to show what
field(s) will be listed vertically (in the rows)
22
(No Transcript)
23
Filtering

• You can easily filter the results by choosing the
  arrow drop downs in the headings

24

• Clicking the plus or minus sign in front of the
  vendor code or ledger name expands or contracts
  the displayed information (in Excel 2007)

25
Creating a PivotTable (in Excel 2003)
26
Excel 2003 has a more customary wizard

• If you have selected either the full spreadsheet
  or any cell in the sheet, it automatically fills
  in the range of the sheet

27
Excel 2003

• This screen of the wizard prompts you where to
  put the new PivotTable, either on the same sheet
  or as a new sheet in the same workbook.

28
Excel 2003
29
Excel 2003
30
Excel 2003
31
PivotCharts

• With simple enough results, you can also use
  PivotTables to create PivotCharts to display the
  information graphically.

32
Thats all

• Any Questions?