About using Microsoft Query to retrieve external data

What is Microsoft Query?

Microsoft Query is a program for bringing data from external sources into other Microsoft Office programs — in particular, Microsoft Excel. By using Query to retrieve data from your corporate databases (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) and files, you don't have to retype the data you want to analyze in Excel. You can also update your Excel reports and summaries automatically from the original source database whenever the database is updated with new information.

Types of databases you can access     You can retrieve data from several types of databases, including Microsoft Access, Microsoft SQL Server, and Microsoft SQL Server OLAP Services. You can also retrieve data from Excel lists and from text files.

In Excel, you can also retrieve data from Web pages, but you don't need Query to do this. For information about retrieving data from Web pages, see Excel Help.

Selecting data from a database     You retrieve data from a database by creating a query, which is a question you ask about data stored in an external database. For example, if your data is stored in an Access database, you might want to know the sales figures for a specific product by region. You can retrieve a part of the data by selecting only the data for the product and region you want to analyze and omitting the data you don't need.

Example of selecting data in Query

Callout 1 Columns selected in Query Wizard

Callout 2 Selected columns brought into Excel

Updating your worksheet in one operation     After you have external data in an Excel workbook, whenever your database changes, you can refresh (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.) the data to update your analysis — without having to recreate your summary reports and charts. For example, you can create a monthly sales summary and refresh it every month when the new sales figures come in.

Using Query to retrieve data     Bringing external data into Excel with Query takes three steps: First you set up a data source to connect to your database, then you use the Query Wizard to select the data you want, and finally you return the data to Excel where you can format it, summarize it, and create reports from it.

ShowSetting up data sources

What is a data source? A data source (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) is a stored set of information that allows Microsoft Excel and Microsoft Query to connect to an external database. When you use Query to set up a data source, you give the data source a name, and then supply the name and location of the database or server, the type of database, and your logon and password information. The information also includes the name of an ODBC driver (Open Database Connectivity (ODBC) driver: A program file used to connect to a particular database. Each database program, such as Access or dBASE, or database management system, such as SQL Server, requires a different driver.) or a data source driver (data source driver: A program file used to connect to a specific database. Each database program or management system requires a different driver.), which is a program that makes connections to a specific type of database. For some types of external databases (OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) cubes), all you need to do to retrieve the data is to set up a data source.

How Query uses data sources     After you set up a data source for a particular database, you can use it whenever you want to create a query to select and retrieve data from that database — without having to retype all of the connection information. Query uses the data source to connect to the external database and show you what data is available. After you create your query and return the data to Excel, Query retrieves the data and provides the Excel workbook with both the query and data source information so you can reconnect to the database when you want to refresh the data.

Diagram of how Query uses data sources

A data source is the means through which Query and Excel connect to a specific database and retrieve data.

ShowDefining your query

Use the Query Wizard for most queries     The Query Wizard, which is part of Microsoft Query, is designed for users who are new to creating queries. The wizard makes it easy to select and bring together data from different tables and fields in your database. After you select the data, you can also use the wizard to filter and sort the results of your query. You can use the Query Wizard either to create a complete query or to start a query that you make further changes to directly in Query.

Work directly in Query for other types of queries     If you are familiar with creating queries or you want to create a more complex query, you can work directly in Query. You can use Query to view and change queries you start creating in the Query Wizard, or you can create new queries without using the wizard.

Use Query directly when you want to create queries that do the following:

Creating an OLAP cube from the data in a query     On-Line Analytical Processing (OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.)) is a way of organizing data for querying and reporting instead of processing transactions. In an OLAP database, data is organized hierarchically and stored in cubes (cube: An OLAP data structure. A cube contains dimensions, like Country/Region/City, and data fields, like Sales Amount. Dimensions organize types of data into hierarchies with levels of detail, and data fields measure quantities.) instead of tables.

You can use Query to create an OLAP cube from the data in a query so that it takes less time and effort to create Microsoft Excel reports based on the data. To create an OLAP cube, you run the OLAP Cube Wizard from the Query Wizard or directly from Query. You can return the data to Excel as a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources including ones external to Excel.).

ShowWorking with the data in Microsoft Excel

After you create a query in either the Query Wizard or Microsoft Query, you can return the data to a Microsoft Excel worksheet. The data becomes an external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) or a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources including ones external to Excel.) that you can format, analyze, and refresh.

Formatting and analyzing the data     In Excel, you can use familiar tools such as the Chart Wizard or automatic subtotals to present and summarize the data. You can format the data, and your formatting is retained when you refresh the external data from the source database. If you add columns of formulas to the external data range, the formulas can be copied automatically to any new rows that are added to the range when you refresh the data. You can use your own column labels instead of the field names, and add row numbers automatically. For information about controlling the formatting and layout of an external data range, see Excel Help.

Reusing and sharing queries     In both the Query Wizard and Query, you can save a .dqy query file that you can modify, reuse, and share. Excel can open .dqy files directly, which allows you or other users to create additional external data ranges from the same query.

If you want to share an Excel summary or report that is based on external data, you can give other users a workbook that contains an external data range, or you can create a report template (report template: An Excel template (.xlt file) that includes one or more queries or PivotTable reports that are based on external data. When you save a report template, Excel saves the query definition but doesn't store the queried data in the template.). A report template lets you save the summary or report without saving the external data so that the file is smaller. The external data is retrieved when a user opens the report template. For information about creating report templates, see Excel Help.

Refreshing external data     When you refresh (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.) external data, you run the query to retrieve any new or changed data that matches your specifications. You can refresh a query in both Query and Excel. Excel provides several options for refreshing queries, including refreshing the data whenever you open the workbook and automatically refreshing it at timed intervals. You can continue to work in Excel while data is being refreshed, and you can also check the status while it's being refreshed. For information about refreshing external data in Excel, see Excel Help.

 
 
Applies to:
Excel 2003