Page 236 - Excel Workbook for Dummies

23_798452 ch16.qxp 3/13/06 7:40 PM Page 219
Chapter 16: Building and Maintaining Data Lists 219

Figure 16-4:
The Query
Wizard —
Filter Data
dialog box
after speci-
fying the
criteria by
which to
filter the
extracted
data.

15. Select the ProductName field in the Sort By drop-down list box and with the
Ascending option button selected, select the Next button to open Query Wizard –
Finish dialog box.
The Query Wizard — Finish dialog box enables you to save the Query as well as to
designate where to view the records extracted during the query to the external data-
base (either in a Microsoft Query window or the Excel worksheet).
When you save the query, Excel saves it in a separate query file (indicated by the .dqy
file extension) that you can then later rerun in any workbook file simply by selecting
its query filename in the Select Data Source dialog box (Data➪Import External Data➪
Import Data).
Note that Excel automatically saves the data source definition as file (indicated by
the .dsn) when you next save the current workbook but not the query itself.
16. Select the Save Query button to open the Save As dialog box. In the Save As dialog
box, edit the suggested filename, Query from Product Query.dgy to Product Query.dqy
before you click the Save button to save the query in the Queries folder.
Excel closes the Save As dialog box and returns you to the Query Wizard — Finish
dialog box, where the Return Data to Microsoft Excel option button is selected by
default.
17. Click the Finish button to close the Query Wizard and open the Import Data dialog box.
This dialog box enables you to select the cell range within the current worksheet
where the extracted data records are to appear (starting at cell A1 by default) or to
select a new worksheet in which to extract the records.
18. Click OK in the Import Data dialog box to extract the records from the Northwind
Access database into the current worksheet starting at cell A1.
Excel performs the query, extracting the fields you designated from the records where
the unit price is greater than or equal to $7.45 into Sheet1 beginning at cell A1, as
shown in Figure 16-5. At the same time, the program displays the External Data toolbar
with tools that enable you to edit the query (at which time you can include different
fields or vary the filtering criteria) as well as to refresh the extracted data by rerun-
ning the query itself.
19. Save the data extracted by the external data query under the filename Solved 16-8.xls
in your Chapter 16 folder in the My Practice Spreadsheets folder and then close the
workbook file by exiting Excel.