MS Access Database Queries: The Economy of Parameter Queries

Written by: Curt Smothers • Edited by: Michele McDonough
Updated Jan 7, 2010 • Related Guides: Microsoft | Microsoft Access | Database

MS Access database queries help us boil down, manipulate and use our data in instant and powerful ways. Using the database query design object, we can specify just about any variation of our data we need. However, we gain control and reduce our query inventory by using the parameter query.

So much data, so many choices

click to enlarge

MS Access gives us a variety of options in choosing how we display and maintain our data. Those options are exercised through its objects (tables, forms, and reports), but those objects are brought under control through the query. Select queries instruct the database to display only the data records and fields that suit or purpose. We save our queries so that we can ask the same questions and instruct our database to give us our answers the same way each time.

Avoid query bloat

Once we specify a single criterion or even multiple criteria and save it as a query, that query joins our inventory of objects, but it might need to be changed as time goes on. For example, if you have a query that needs to reflect updated information (sales records for this month), you’ll have to edit that query each month. Factor in other variables, and pretty soon you might even give up and start from scratch to avoid having to keep track of not only your data, but also all the queries you apply to it.

Slim down with the parameter query

The difference between a parameter query and your run-of-the-mill select query is best illustrated as follows: Using the example of a sales database, you could make a query that specifies “[SalesRegion]=”Cincinnati”, and then tell the query to display fields in your table and do a calculation. That will work fine, but what if you have a dozen sales regions? With a dozen sales regions, you could make a dozen queries, or you can do it the economical way and use one query: the parameter query.

Here's how!

click to enlarge

Making a parameter query in MS Access is the shortcut you need to cut down the number of your queries. In the example above you could in your query design table, instead of “Cincinnati” you would enter the criteria “[Which Sales Region?]. After you save your query and run it, you get a dialog box that waits for you to enter the specific sales region you need to look at. You can also specify other parameters within that same query and get a series of dialog boxes to respond to.

The query with a difference

So, the parameter query differs from other select queries in that the parameter query is not activated until you specify what you want the database to display (i.e., you specify the parameters!). You can combine parameter queries with totals and crosstabs that will display totals, counts, etc., for each new parameter you enter. (This, by the way, is where the MS Access Database leaves its spreadsheet cousin in the dust.)

If it's time to clean house...

Parameter queries, then, can streamline your database query inventory and be used over and over to display changing information in a consistent and useful way. If your MS Access Database is encumbered with too many individual select queries, consider changing them to parameter queries. Just go into your query design tables and remove the “answers” (specifics) with the “questions” (parameters).

Learn how at...

For more tips and tricks, check out the collection of Microsoft Access tutorials found here at Bright Hub. Also, see Microsoft’s 50-minute tutorial on designing parameter queries. You can run your own sample parameter query in MS 2007’s “Northwind” database by following the steps on Microsoft’s tutorial page.