Figure 2-13. Microsoft Query screen

2.4. Using Microsoft Query to Gather Data

Now that you have tried the Excel user interface, I want to introduce you to the Microsoft Query interface. Use the Microsoft Query interface instead of the Query Wizard when you need more control over the query. For example, you might want to add a calculated field or perform a complex join in your query. Also, while you can create a parameter query with the query wizard, you must edit a parameter query with the Microsoft Query interface. So, let's try a simple example to demonstrate how to change the query to a parameter query. Go back to your Query results from the first example, or go through the steps again (see Figure 2-10). Once you see the results, right-click in the result data and select Edit Query. Get to the final screen and select "View data or edit query in Microsoft Query." You will see the screen in Figure 2-13. In the "Criteria Field and Value" section, select Freight for the field and >100 for the value. To change this to a parameter, replace >100 with >[Amt] (you can use any name that does not represent a column in the Query in brackets). After you click off of that field, it will ask you for the parameter amount. This time, type in 500 for the amount, and press Enter. When you are finished, go to the File menu and select "Return Data to Microsoft Office Excel."

Creating a query as a parameter query is useful for changing the data that you look at regularly; using a parameter query is much easier than continuously editing the query in Microsoft Query. You can bring up the Parameters menu at any time by right-clicking in your data range and selecting Parameters. Excel allows you to choose whether you want to be prompted for the parameter, to use a particular number, or to refer to a cell to obtain the value. If you are going to change the value on a regular basis, I suggest using the option that looks in a cell for a value. This avoids prompting and also allows you to use VBA to refresh the query whenever you change the value of that cell.

Now that you are back in Excel with a parameter query, to set your parameter to come from a cell within Excel, select rows 1 through 3 and select Insert Rows from the top menu to move your data down three rows. In cell A1, type Amount and in cell B1, type 300. Then right-click in your data range and select Parameters from the menu. Click on the "Get the value from the following cell:" box, and click on cell B2 (Figure 2-14). Right-click on your data and select Refresh Data, and Excel will pull in the additional records (additional as compared to the original parameter of 500). Once you have done this, you will be able to change the criteria on the fly and refresh the result set.

Figure 2-14. The ExternalDataRange Parameters dialog box