2.2. Using Database Queries

Database Queries are valuable when you need more control over the data that is returned. Here is a quick example scenario where you can use a Database Query; this example uses the Northwind Database that comes with Access. Let's assume that your job is to review orders where the freight cost is over $100. There is a Query already designed in the Northwind Database called Orders Qry where the freight column is defined. You could bring in the entire table and search for records where the freight is over $100, but that would be time-consuming and error prone. It's simpler to make the computer do the work.

Choose New Database Query from the Import External Data submenu of the Data menu to get the screen shown in Figure 2-4. Since we want to use the Northwind Database, select MS Access Database from this dialog box and press OK, making sure that the box at the bottom of Figure 2-4 is checked to have the Query Wizard write the queries. The Northwind Database is in the Microsoft Office Samples Folder, as shown in Figure 2-5. After you select the Northwind Database, you get a list of all the tables and queries available in the database. For this example, you want to select the query called Orders Qry. You can expand a table or query to see all of the available fields, enabling you to select only the fields that you want. For this example, we want all of the fields, so click once on the name of the query and press the > button to place all of the fields in the query (Figure 2-6). To remove any of the fields, click on the field to remove and press the < button. In this case, since we want all of the fields, simply press the Next button.

Figure 2-4. The Data Source selection box used to create a new database query

Figure 2-5. The Select Database dialog box

Figure 2-6. The first screen of the Query Wizard

The next screen in the Query Wizard is the Filter Data screen (Figure 2-7). On this screen, pick a column to filter and select the criteria. For this example, select Freight as the column, "is greater than" as the comparison operator, and enter 100 as the amount. The drop-down box to the right of the comparison operation shows the values in the database; you can override these by just typing in the box. Once you have done this, press the Next button.

The next screen (Figure 2-8) sorts the records. Select Freight as the "Sort by" field, and select Descending to sort the records from the most to the least freight cost. When you have done this, press the Next button. On the final screen, select whether to return the records to Excel, edit the query, or create an OLAP Cube. In addition to these choices, you may also choose to save the query so that you can easily access it from other Excel workbooks. For this example, select "Return Data to Microsoft Excel" and press Finish (Figure 2-9).

You are now out of Microsoft Query and back in Excel. Excel brings up a dialog box asking where to put the data (Figure 2-10). You can chose either an existing worksheet or a new worksheet. If you select an existing worksheet, you can select the cell where the import begins. The resulting records can also produce a PivotTable, which will be covered later.

This, again, is a relatively simple example; you can perform much more complex queries. However, this gives you a good example to try with the data that is already

Figure 2-7. The Filter Data screen of the Query Wizard

Figure 2-8. The Sort Order screen of the Query Wizard

Figure 2-9. The Finish screen of the Query Wizard

Figure 2-10. Directing Excel as to where to place the data pulled from Microsoft Query

on your computer. This method also makes it very easy to change criteria. Right-click anywhere in the result set and select the Edit Query option to bring the wizard back up and make changes to the query, such as selecting different columns, changing the criteria, adding new criteria, etc. If you change the criteria, you'll notice that any field that has criteria selected is in boldface. In addition to using the wizard, you can also change the query through VBA. The object created by a New Database Query is also both a QueryTable object and a named range. If you do not like the default name, you can change it in the Data Range Properties dialog box, which is accessed by right-clicking in the data range and selecting Data Range Properties.