Database Functions

Database Functions

Pivot Table

Retain the data entered in the previous exercise.

A Pivot Table extends the capability of individual database functions by presenting the data in summary form. It divides the records in a list into categories, then compute summary statistics for those categories.

The Pivot Table in the above figure displays the number of employees in each location according to job title. You can create a Pivot Table by using PivotTable wizard. You indicate the field names for the row and column labels. You also indicate the field on which the computation is to be based and the means of computation (a count of names)

Using Pivot Table

  1. Pull down the data menu, click the PivotTable to produce step 1 of the PivotTable wizard. The option button indicates the PivotTable will be created  from data in a Microsoft Excel List or database.
  2. Click next command button to move to step 2 of PivotTable wizard. You will see a dialog box where database has already been entered in the range text box.
  3. Click the next command button to move to step 3, then select layout

o       Click the title field button and drag it to the row area

o       Click the location field button and drag it to the column area

o       Click the salary field button and drag it to the data area. (Sum is the default computation for a numeric field, such as salary. Count is the default computation for a text field, such as name)

o       Click the finish command button

Modify the Pivot table

Click and drag the name field button to the data area. The button displays “Count of name”

Click and drag the gender field to the page area

Click and drag the salary field button out of the data area

Click ok and finish command button.

The Pivot Table changes to display the number of employees for each location-title combination.

The page field

A page field adds a third dimension to the pivot table. Unlike items in the row or column head, items in a page field are displayed one at a time. Creating a page field on gender for example enables you to view the data for each gender separately, by clicking the drop down arrow on the page field list box, then clicking the appropriate value of gender.

Pivot the table

Click and drag the gender button next to the location button. The page field disappears and there are two column fields, gender and location.

Click and drag the location button to the previous location of gender field to make location a page field. The arrangement makes it easy to see the numbers of male and female employees in each job classification.

Boldface the row and column labels of the Pivot table in order to improve its appearance.

Center the column labels. Center all values in the Pivot table under their respective headings.

Save the workbook.