Building and using a relational database in Excel (with a little help from MS Query)

Building the queries

Query 4 -- A parameterized query to list of customers in a zip code that is specified dynamically

Now that we know how to link to MS Query, how to add tables to a query within MS Query, how to add fields to the query, and how to return data to Excel, we can expedite building this query.  Once in MS Query add the Customers table to the query.  Then, use View | Criteria to see the Criteria pane.  This works very similar to where we select the fields to display.  In the Value row, one can either enter a specific value or defer the decision to the time when the query is executed.  To do the latter, enclose the text in square brackets as shown below, making sure to not use the name of an actual field.

Select File | Return data to Microsoft Office Excel to return to Excel.  MS Query will try and run the query and since it needs to know what zip code to use, it will pop up with a dialog box like:

Enter any value and click OK.

Back in Excel, note that the dialog box does not offer a choice to Create a PivotTable report...  This is because PivotTables do not support parameterized data sources.

Also, note the Parameters... button.  This makes it possible to tell Excel where to find the value it needs to run the query.  Click the button to get

This is where we get to see the real power of a parameterized MS Query.  Select the last option Get the value from the following cell.  This enables the field to specify the cell and the checkbox Refresh automatically when cell value changes.  As the name implies, the query will be rerun whenever the parameter value in the specified cell changes.

Select some cell, say B4, and select the checkbox.

Now, enter another zip code, say 14627 and the result will change to