Integrating Excel and Access

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."

Figure 2-13. Microsoft Query screen

Creating a query as a parameter ...