Chaining Parameter Queries

In Parameters in External Data Queries, Nick Hodge shows how to change a query table based on input from a cell. In this example, one parameterized query will be based on another parameterized query. That is, when you change one, they both change.

Start with two query tables on the same worksheet. These two tables are the Categories and Products tables from Northwind.mdb. The Categories query table will have a prompt-type parameter in which a user can enter the desired category name to show the description for that category. The Products query table will have a cell-based parameter that shows all the products for the selected category.

worksheet showing two query tables

The CommandText properties for these query tables look like

SELECT Categories.CategoryID, Categories.CategoryName, Categories.Description
FROM ‘C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind’.Categories Categories
WHERE (Categories.CategoryName=?)
SELECT Products.ProductID, Products.ProductName, Products.CategoryID
FROM ‘C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind’.Products Products
WHERE (Products.CategoryID=?)

The first query table’s parameter was changed to show a meaningful prompt. The second table’s parameter was changed to point the Category ID cell in the first table.

parameter dialog for first table

parameter dialog for second table

When the first table is refreshed, the user is prompted for a category name. The second table will be refreshed to show all the products for that category automatically.

refreshing first table dialog

both tables changed after first refreshed

Posted in Uncategorized

8 thoughts on “Chaining Parameter Queries

  1. How does one control result set to 1:n? IE Using your example returns as many CategoryID, CategoryName, Description items as there are items in ProductID, ProductName, CategoryID list. In other words a flat file and not normalized…

    Using Office 2000 Premium on Windows XP Home

  2. Those are two different query tables. The left query table that comes from the Category table in Access will only ever be one row. The right qt from the Products table in Access is the many side of the relationship. Maybe I don’t understand the question.

  3. I don’t seem to be able to use a range name for the parameter source. Poor form by Old’Bill Gates, he didn’t finish the job neatly. Fingers crossed for Excel 12

    Sorry this is a bit old but the tips are great.

  4. Another serious oversight IMHO:

    If you get “wrond kind of … than what was expected” error; instead of being returned to the query editor you must start over from scratch.

    THIS SUCKS! :(

  5. […] There is an excellent article on Daily Dose of Excel that demonstrates how I created for itenerate & intermittant inquistors of my sales data a quick and dirty report; not to mention the basis for ratio studies, setups, etc already in our favorite analysis tool. There is a wealth of data from those old cards. It sure is handy to have this data at the ready and on demand. With the entry of a few parameters I can have whatever is in that data file based on those parameters and current data! • • • […]

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.