MS Access Query Def - Constructing Parameter Queries with Microsoft Access Databases
So much data, so many choices
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!
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.
This post is part of the series: MS Access: Using Parameter Queries
The parameter query is an efficient way to access your database without the need for multiple select queries. This series offers advice and pointers for creating, using and attaching parameter queries to Microsoft Access database objects.
