Databasics VII: Parameter Queries

If the title of this tutorial sounds a little intimidating, never fear. Toss the term ‘parameter query’ around at a party and you may get a few gasps of admiration, but understanding and using this highly useful type of query is no big deal.

A parameter query is interactive – it prompts the user for information when it’s run. You can use a parameter query to ask the user for criteria for retrieving records or for a particular value to insert in a field. This makes parameter queries great for generating dynamic, on-the-fly results.

If you think of the queries we’ve examined in earlier articles in this series, while the data may change, the query itself is static:

• Which countries have a population over 20 million and an area of over 2 million square kilometres?
• Which country has the smallest population density?

With parameter queries, both the data and the queries may change over time:

• Which countries have a population between x and y?
• Which countries joined the United Nations in the nth decade of 2000?

You obtain the values for those variables – x, y and n – by asking the user to supply them each time the query is run.

Creating a parameter query

Let’s take a look at some parameter queries in action, using Microsoft Access, which provides tools that let us do this job easily. We’ll use the Countries database we’ve used previously (you’ll find a download link at the bottom of this page) and start by creating a non-parameter query to answer the question “Which countries have a population greater than one hundred million?” We’ll then transform that query into a parameter query and contrast the results.

Here goes:

1. Open the Countries database in Access.
2. In the main Database Window, click Queries in the Objects list.
3. Double-click Create Query In Design View to open the Query window and Show Table box.
4. Select the Countries table to add it to the Query window, click Add, and then click Close.
5. Click Name in the list of fields, hold down the Ctrl key and click Population. Drag the two fields to the left-hand column in the query design grid to add the fields to the query.
6. In the Population column in the grid, type:

>100000000

in the Criteria row.

7. Click the Run button (it has an exclamation mark on it) to run the query. You should see 11 countries listed, each with a population greater than 100 million.

That’s our simple, non-parameter query, which we created by providing a constant (100 million) as the matching criteria. Now let’s change it into a more flexible parameter query, which uses variables provided by the user instead of a constant.

1. Click the View button (it’s the left-most button on the toolbar) to return to Design View, so we can modify the query.
2. Delete the contents of the Criteria cell in the Population column and replace it with:

>[Show countries with a population over:]

You may need to resize the column (by dragging the right edge of the column header to the right) so you can see the entire contents.

3. Click the Run button. You’ll be presented with a dialog box containing the prompt, “Show countries with a population over:” Fill in any value you like and click OK to see the results.
4. Save this query by clicking the Save button and naming it Population over x.

Parameter tips

Neat, huh? Let’s examine the query to see how we did this.

The criteria consists of the greater than operator (>) followed by the prompt we wish to use enclosed in square brackets:

>[Show countries with a population over:]

That’s simple enough, but there are two important things to keep in mind when you create your parameter prompts. First, be succinct. Access lops the prompt off at 37 characters, so if you are verbose you’ll end up with shabby-looking, truncated prompts.

Second, be as clear as possible. Although you want to be succinct, don’t become cryptic. For instance, you could just as easily use the criteria:

>[Pop greater than:]

or even

>[Pop?]

Those will work, but if someone else tries to use your database or you use the query only occasionally, a more informative prompt will avoid confusion and ensure the correct input.

Obtaining a range

Your parameter queries are not restricted to a single prompt. You can use them to find a range of values or obtain multiple criteria.

Here’s a query which answers the question, “Which countries have a population between x and y?” by prompting for a lower and an upper value:

1. If you still have the results of the Population over X query open, click the View button to return to Design View. If you closed the query after saving it, highlight it in the Queries list and click Design to open it in Design View.

2. In the Criteria cell of the Population column, add:

AND <[and a population under:]

The full criterion should now read:

>[Show countries with a population over:] AND <[and a population under:]

3. Run the query and you’ll be prompted twice. Enter a lower limit in the first dialog and an upper limit in the second, then click OK to see the results.

4. From the File Menu choose Save As and name the query Population Range. By saving the query, you’ll be able to run it repeatedly, supplying different values each time.

This range query is similar to queries we’ve explored before, using two comparison operators (> and <) and the logical operator AND, but instead of using constant values, it substitutes two parameters obtained via dialogs.

A range example using dates

Let’s try one more range parameter query, this time using dates. This one will answer questions such as “Which countries joined the United Nations in the nth decade of 2000?”

1. Create a new query in Design View.
2. Select the Countries table and click Add to add it to the query, then close the Show Tables dialog.
3. Drag the fields Name and JoinedUN onto the query grid.
4. In the Criteria cell of the JoinedUN column, type:

>[Joined UN after what date:] And <[and before what date:]

5. Run this query and respond to the prompts by typing dates in the format dd/mm/yyyy. To view all countries that joined the United Nations during the nineties, for instance, enter the values 31/12/1989 and 1/1/2001. If you use mm/dd format instead of dd/mm format for dates, you’ll want to make that first value 12/31/1989, of course.
6. Save this query and name it Date Range.

You can, of course, use other operators (<>, =, <= and so on) and logical expressions (OR, NOT) in range queries.

Multiple values

As well as matching a range of values in a single field, you can use parameter queries to match values in more than one field.

Let’s modify the Date Range query to let us not only specify a date range, but also limit the answer to countries over a certain population.

1. Open the Date Range query in Design View.
2. Drag the Population field from the field list onto the query design grid to the right of the JoinedUN field. Our query now contains three fields – Name, JoinedUN and Population.
3. In the Criteria cell of the Population column, type:

>[and has a population over:]

4. Run the query.

You’ll be presented with three prompts, asking for lower and upper dates and then a lower population limit. Note that this query in essence uses the AND logical operator to extend the query, so it’s the equivalent of: Show me countries that joined the UN between date x AND date y AND which have a population over n.

Running a parameter query: A parameter query prompts the user for one or more criteria. In this example, the user specifies a population range and the query returns all matching records.

Avoiding endless dialogs

Try experimenting with parameter queries on your own. You’ll find they add enormous flexibility to the types of information you can wheedle out of your database. A tourist bureau, for example, can use them to provide information to the public on local events occurring this week. A small business can find all those customers who placed orders over a specific amount in the previous quarter. You’re sure to come up with instances where you can use them with your own data.

The only drawback with these queries is that you can end up peppering your users with endless, rather unattractive prompt boxes as you try to collect all the necessary parameters.

There’s good news. With a little fast footwork, you can avoid these dialogs and replace them with a single, good-looking dialog of your own design. I’ll show you how in the next article in this series.