Creating a Pivot Table from an External Database

 

Introduction.  PivotTables can also be developed from an external data source such as an Access database.  This can be advantageous for many reasons but the major ones are
  • Use the computing power of Excel to compute performance measures of the data.
  • Use the concomitant charting features to display results.

There are many situations where doing these sorts of things can be advantageous.  We will focus on data associated with entering students, their qualifications and their financial aid awards.

The URecords Database.  You should be able to get a copy of a simplified set of data that has been used to simulate many of the database requirements associated with operating a university.  Since I don't know whether you are using Excel 97 or 2000 you will need to let me know before I send the data source to you. 

Within this moderately complicated database there is a query that joins basic application information about a potential student with information about their financial aid.  This query results from a join clause in SQL, which you may or may not know about.  The fields in the query are contained in the following table.  A brief description accompanies the fields that are likelier to seem obscure.

 

Field Description
FirstName  
LastName  
SchoolInterest In which school within the university has the potential student expressed interest.
Type Are they a going to be an entering freshman or a transfer student?
StudentNumber  
SATMath  
SATVerbal  
QuinnipiacContribution How much money has Quinnipiac committed to providing this coming year?
FamilyContribution How much money is the family considered to be able to contribute?
PersonalContribution How much will the student contribute towards their annual costs?

 

Remember, in such a situation the tables are going to be very large and hopefully normalized.  The data source is definitely going to be a database system of some sort, though it could be quite primitive.  Remember database programs do not have the sort of computing capability that is built into Excel. 

With this information you would be able to answer many different questions of interest.  Just a few that we are likely to focus on are listed below.

  • How do SAT scores break down according to which school the student's  professed interest about within the university.
  • How does financial aid break down according to the school within the university.
  • Is more financial aid being offered to students that the university is more likely to want to have in attendance?
  • What are some of the differences between students that enter as freshmen and those that transfer?

Hopefully, this also gives you some ideas of how other information could be included or used.

Now we will start on developing the PivotTables necessary to carry out some analysis.  Connecting to the data source can be a bit labyrinthine, but it is definitely worth it.  We are also going to want to do some other adjustments to develop the inputs for the PivotTable such as include a computed field where we sum the Verbal and Math SAT scores to get a Total SAT score.

Connecting to the Database.  You want to start a new Excel workbook and work through the following steps.

  1. In the Data menu
  2. Select the PivotTable and PivotChart Report entry to start the wizard.
  3. Select the External Data Source radio button near the top of the form
  4. and select the PivotTable radio button near the middle of the form as shown in the following image.

 

 

  1. Click on the Next > button to move to step 2.
  2. You should see a dialogue form like the following.

 

 

  1. Click on the Get Data... command button.
  2. You should now see a dialogue form like the following.

 

 

  1. Select the MS Access Database*
  2. Click on OK to get the next form where you find the database.

 

 

  1. Now you should use the file and directory picker boxes to select the URecords2000.mdb database depending on where you saved it.
  2. Click on OK to get the next dialogue.  Be a bit careful because the following image shows some of the selections you are about to make.

 

 

  1. Select the qryJoinAdmissionsFinancialAid in the Available tables and columns list box.
  2. Click on the command button with  >  on it to move all of these columns into the query.
  3. Click on the Next button to get the following dialogue.
  4. Click on the Next button since you don't want to create any filters at this point.  You'll get another dialogue.
  5. Click on the Next button again because you don't want to sort the data at this point.  You will get the following dialogue.

 

 

  1. Select to Return Data to Microsoft Excel.
  2. Click on the Finish button.  This will take you back to Step 2 of the original PivotTable Wizard.
  3. You should now select the Next button in the PivotTable Wizard.  This will give you the following dialogue for Step 3.

 

 

  1. You may as well select the Finish button in order to set this up initially. 

This is largely due to the fact that you want to do some things in the Field Settings.  This will give you the following image on your spreadsheet.

 

  1. For the present you want to drag the SchoolInterest into the Row Fields.
  2. Drag the Type into the Column Fields.
  3. Drag the Quinnipiac Contribution into the Data Items.

This will give you the following image in your spreadsheet.

 

 

This in itself is interesting and would be of even more interest if we had a real data source.  We will take a little bit of time in class to talk about this.

At this point in time you are likely to want to save your spreadsheet in an appropriate directory with some name like URecords.xls.

 

Inserting a Calculated Field.  It is often the case that you want to use existing fields to create a new field before you analyze it.  In this data set we have at least one such obvious instance with the SAT scores.  The data contains the individual Math and Verbal SAT scores, but it is typical to use the Total SAT score when making decisions.  The following steps and images will lead you through the steps necessary to develop a TotalSAT sore within the data set.  While it would be possible to use the SQL Update command to create this field before you connect the data to Excel we will assume that you either don't know how to do this or forgot to do it.
  1. Select the PivotTable menu in the PivotTable toolbar.
  2. Select the Formulas item in the menu and then
  3. select the Calculated Fields item in the submenu that appears.

The following image should help.

 

 

You should now get the following dialogue form, though it is important to remember I have already modified a couple entries..

 

 

  1. In the Name: combo box type TotalSAT
  2. In the Formula: text box you should enter = SATMath + SATVerbal
  3. Click on the Add button to add the field to the Fields: in the list box.
  4. Click on the OK button.

Now you have the TotalSAT score available for use in the PivotTables.