Page 233 - Excel Workbook for Dummies

23_798452 ch16.qxp 3/13/06 7:40 PM Page 216
216 Part IV: Managing and Securing Data

12. Redisplay all the records in the data list on the Employee Data List worksheet,
position the cell cursor in cell A1, and then save your work in a new workbook
called Solved16-7.xls in your Chapter 16 folder in the My Practice Spreadsheets
folder and close the workbook file.

Querying External Database Tables

Excel makes it possible to query other external databases to which you have access
and then extract the data that you’re interested in into your worksheet for further
manipulation and analysis. The external databases that you can query can be as
simple as those you maintain with a dedicated database-management program such
as Microsoft Access all the way up and including the corporate database itself.
To create a query that acquires data from an external database, you must complete
two procedures:
Define the data source; that is, the external database that contains the data you
want to query
Specify the query itself, including all the columns of data that you want extracted
along with the criteria for selecting them

Try It

Exercise 16-8: Querying an External Data Table
Create a blank worksheet in a new workbook. In your Chapter 16 folder is a copy of
the sample Northwind database that accompanies Microsoft Access. Use the blank
workbook to practice querying records in Northwind:

1. Choose the Data➪Import External Data➪New Database Query menu command. If
an alert dialog box appears informing you that the Microsoft Query feature is not
currently installed and asking you if you want to install it, click Yes.
After Microsoft Query is installed, the Microsoft Query Choose Data Source
dialog box appears with the <New Data Source> item selected on its Databases
tab.
2. Select OK in the Choose Data Source dialog box to create a new data source.
The Create New Data Source dialog box appears with the What Name Do You
Want to Give Your Data Source? text box selected. By naming your data source
definition, you can reuse it to perform the same query on the external database
whenever the need arises.
3. Type Product Query as the name for the data source.
As soon as you begin typing the data source name, the Select a Driver for the
Type of Database You Want to Access drop-down list box becomes active. Here,
you select a driver for the type of database program used to create the database
tables you want to query.
4. Select Microsoft Access Driver (*.mdb) on the Select a Driver for the Type of
Database You Want to Access drop-down list and then select the Connect button.

Because you selected the Microsoft Access Driver, Excel now opens the ODBC
Microsoft Access Setup dialog box (you see a slightly different dialog box when