Excel® 2007 PivotTables and PivotCharts

Appendix 2. Using Microsoft Query with PivotTables

Understanding Microsoft Query

If you want to build a PivotTable using a sorted, filtered, subset of an external data source, you must use Microsoft Query to specify the sorting and filtering options and the subset of the source data that you want to work with.

Databases such as those used in Microsoft Access and SQL Server are often very large and contain a wide variety of data scattered over many different tables. When your data analysis requires a PivotTable, you can never use an entire database as the source for the report. Instead, you can extract a subset of the database: a table or perhaps two or three related tables. You may also require the data to be sorted in a certain way and you may also need to filter the data so that you only work with certain records.

You can accomplish all three operations — extracting a subset, sorting, and filtering — by creating a database query. In Excel, the program that you use to create and run database queries is Microsoft Query. You learn how to use Microsoft Query in this appendix. This section gets you started by introducing you to various query concepts and how they fit into Microsoft Query.

Data Source

All database queries require two things at the very beginning: access to a database and an Open Database Connectivity, or ODBC, data source for the database installed on your computer. ODBC is a database standard that enables a program to connect to and manipulate a data source. An ODBC data ...