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.

1. Open the workbook named Forecasting.xlsm located at %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Starter Files
2. Add a new worksheet to the workbook and name it ForecastData
3. Click on the Data tab in the ribbon and click the Get External Data button
4. Select From Microsoft Query under the From Other Sources button
5. On the Databases tab, select <New Data Source> and click OK
6. Name the data source Budget Database
7. Select the driver named Microsoft Access Driver (*.mdb, *.accdb)
8. Click Connect…
9. Click Select… in the ODBC Microsoft Access Setup
10. Select the database named Budget.accdb located at %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Database and click OK

11. Click OK to close the ODBC Microsoft Access Setup dialog box

    Figure 1

    ODBC Microsoft Access Setup

12. Click OK to close the Create New Data Source dialog. You do not need to select a default table.

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.

1. Picking up from where you left off in the previous task, you should be looking at the Choose Data Source dialog box with the Budget Database data source selected.
2. Ensure that Use the Query Wizard to create/edit queries is not checked. The Query Wizard is helpful for simple table-based queries, but it does not allow you to create a parameterized query, which is what we want for this exercise.
3. Click OK in the Choose Data Source dialog to close this window and display Microsoft Query. Note that after you define a data source as you did in steps 5-12, you can use it in the future simply be selecting it in the Choose Data Source dialog box
4. Select Facts Crosstab from the list of tables and click Add.

5. Close the Add Tables dialog

   Select ViewCriteria to show the criteria pane

   Figure 2

   View Criteria

6. Add fields from the Facts Crosstab table (technically it is a query defined in the Budget database) to the query fields by double-clicking on each of them in the table. These will be added to the results area at the bottom of the window. Be sure to add them in the following order:
   1. Account – click the Sort Ascending button in the toolbar after adding this field to order the results by Account.
   2. 1
   3. 2
   4. 3
   5. 4
   6. 5
   7. 6
   8. 7
   9. 8
   10. 9
   11. 10
   12. 11
   13. 12
   14. Account Name
   15. Fiscal Year
   16. Scenario Type
   17. Store
7. Drag the following fields from the Facts Crosstab table to the Criteria Field in the Criteria pane
   1. Store
   2. Fiscal Year
   3. Scenario Type
8. Set the Criteria values as shown below. The brackets around the values designate these criteria values as parameters.
   1. Store: [Store ID]
   2. Fiscal Year: [Year]

   3. Scenario Type: [Scenario]

      Figure 3

      Criteria Values

9. Test the query by selecting RecordsQuery Now. Supply the following values for the parameters:
   1. Store ID = 1
   2. Year = 2009
   3. Scenario = Actual
10. Select FileReturn Data to Microsoft Excel
11. Put the data in the Data worksheet in cell B5.
12. Click OK

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.

1. In cell B1 enter the value: Store
2. In cell B2 enter the value: Year
3. In cell B3 enter the value: Scenario
4. In cell C1 enter the value: 1
5. In cell C2 enter the value: 2009
6. In cell C3 enter the value: Actual
7. Right-click on any cell in the data table and select TableParameters
8. Select the Store ID parameter
9. Choose the option Get the value from the following cell

10. Put the cursor in the selection text box and then choose cell C1 on the Data worksheet

    Figure 4

    Parameters

11. Check Refresh automatically when cell value changes
12. Repeat steps 8-11 for the Year and Scenario parameters
13. Click OK

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.

1. Select cell C1 and change the value from 1 to 2. You should see a slight delay while the query is processed, followed by a refresh of the data in the table.
2. Select cell C2 and change the value from 2009 to 2008. You should see a slight delay while the query is processed, followed by a refresh of the data in the table.
3. Select cell C3 and change the value from Budget to Actual. You should see a slight delay while the query is processed, followed by a refresh of the data in the table.
4. To prepare for the next exercise, set the values for the cells above back to:
   1. Store = 1
   2. Year = 2009
   3. Scenario = Actual