Creating a Pivot Table from an External Database
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
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.
|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?|
|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.
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.
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.
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.
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..|
Now you have the TotalSAT score available for use in the PivotTables.