What is a data source? A data source 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 or a data source driver, which is a program that makes connections to a specific type of database. For some types of external databases (OLAP 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
A data source is the means through which Query and Excel connect to a specific database and retrieve data.
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:
- Select specific data from a field In a large database, you might want to choose some of the data in a field and omit data you don't need. For example, if you need data for two of the products in a field that has information for many products, you can use criteria to select data for only the two products you want.
- Retrieve data based on different criteria each time you run the query If you need to create the same Excel report or summary for several areas in the same external data
— such as a separate sales report for each region — you can create a type of query called a parameter query. When you run a parameter query, you are prompted for the data to use as the criteria when the query selects records. For example, a parameter query might prompt you to enter a specific region, and you could reuse this query to create each of your regional sales reports. - Combine data in different ways You can use Query to combine, or join, the data from different tables in your database. For example, if you have a table of product sales information and a table of customer information, you might join these tables in ways that show which customers have not made purchases recently.
Creating an OLAP cube from the data in a query On-Line Analytical Processing (OLAP) 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 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.
Working 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 or a PivotTable report 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. 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 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.