Exercise 2: Creating and using Parameterized Queries in Excel
It is amazing how many scenarios you can cover using simple data connections such as the one created in Exercise 1. Combined with PivotTables, this is a powerful way to approach ad-hoc data analysis. For those times when you need a more structured approach, more control over the presentation of the data, or when you are working with more complex database structures, you can also employ the capabilities of Microsoft Query. Query has been around for quite some time offers some great capabilities, the most compelling of which is the ability to create parameterized queries. In this section, you’ll learn how to use Microsoft Query to create parameterized queries.
Task 1 – Defining the Data Source
When accessing data with Microsoft Query, you need to have a data source defined for the data source you wish to access. Defining a data source is a one-time activity. That is, once you define a data source, it is available from that point on within any workbook on the computer.
Task 2 – Building the Query
Now that you have a data source defined, the next step is to define the query. If you already have a data source defined, you would skip steps 5-12 in the previous task as move directly in to the first step of this task. Building a query with Microsoft Query is somewhat similar to designing a query in Microsoft Access.
Task 3 – Hook up the query parameters to cells on the worksheet
At this point, you have data on the worksheet, but you have not really achieved much more than you can do using the basic functionality you learned in Exercise 1. To harness the power of this technique you need to map cells on the worksheet to the values used for the parameters.
Exercise 2 Verification
In order to verify that you have correctly performed all steps of exercise 2, proceed as follows:
In this verification, you will test your work by changing the values of the cells that you mapped to query parameters.