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.
The CommandText properties for these query tables look like
SELECT Categories.CategoryID, Categories.CategoryName, Categories.Description
FROM `C:\Program Files\Microsoft Office 2000\Office\Samples\Northwind`.Categories Categories
WHERE (Categories.CategoryName=?)
SELECT Products.ProductID, Products.ProductName, Products.CategoryID
FROM `C:\Program Files\Microsoft Office 2000\Office\Samples\Northwind`.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.
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.
doco:
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
22 April 2005, 5:43 amDick Kusleika:
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.
22 April 2005, 9:14 amdoco:
Yeah, I see my error now - I was being a boner brain again. :rolleyes:
23 April 2005, 10:27 amJan:
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.
16 November 2005, 9:45 amdoco:
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!
17 November 2005, 9:43 amRob van Gelder:
Try the “Query Editor” add-in, available on my website.
It allows you to avoid the dreaded MS-Query and manipulate parameters easier.
Rob
17 November 2005, 11:34 pmdoco:
Rob:
That is way kewl! Thanks.
doco
18 November 2005, 4:51 pmBlog and Document»Blog Archive » Curent Sales Data:
[…] 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! • • • […]
7 January 2006, 12:39 pm