Working with multiple parameter conditions

You aren't in any way limited in the number of parameters you can use in your query. Figure 13.4 demonstrates how you can utilize more than one parameter in a query. When you run this query, you'll be prompted for both a system period and a product ID, allowing you to dynamically filter on two data points without ever having to rewrite your query.

Combining parameters with operators

You can combine parameter prompts with any operator you would normally use in a query. Using parameters in conjunction with standard operators allows you to dynamically expand or contract the filters in your analysis without rebuilding your query. To demonstrate how this works, build the query shown in Figure 13.5.

This query uses the BETWEEN…AND operator and the > (greater than) operator to limit the results of the query based on the user-defined parameters. Since there are three parameter prompts built into this query, you'll be prompted for inputs three times: once for a starting period, once for an ending period, and once for a dollar amount. The number of records returned depends on the parameters you input. For instance, if you input 201201 as the starting period, 201203 as the ending period, and 5000 as the dollar amount, you'll get 1,700 records.

Combining parameters with wildcards

One of the problems with a parameter query is that if the parameter is left blank when the query is run, the query will return no records. One way to get around this problem is to combine your parameter with a wildcard so that if the parameter is blank, all records will be returned.

To demonstrate how you can use a wildcard with a parameter, build the query shown in Figure 13.6. When you run this query, it'll prompt you for a period. Because you're using the * wildcard, you have the option of filtering out a single period by entering a period designator into the parameter, or you can ignore the parameter to return all records.

Using parameters as calculation variables

You are not limited to using parameters as criteria for a query; you can use parameters anywhere you use a variable. In fact, a particularly useful way to use parameters is in calculations. For example, the query in Figure 13.7 enables you to analyze how a price increase will affect current prices based on the percent increase you enter. When you run this query, you'll be asked to enter a percentage by which you want to increase your prices. Once you pass your percentage, the parameter query uses it as a variable in the calculation.

Using parameters as function arguments

You can also use parameters as arguments within functions. Figure 13.8 demonstrates the use of the DateDiff function using parameters instead of hard-coded dates. When this query is run, you'll be prompted for a start date and an end date. Those dates will then be used as arguments in the DateDiff function. Again, this allows you to specify new dates each time you run the query without ever having to rebuild the query.

Using IIf to avoid mathematical errors

To demonstrate a simple problem where the IIf function comes in handy, build the query shown in Figure 13.9.

When you run the query, you'll notice that not all the results are clean. As you can see in Figure 13.10, you're getting some errors due to division by zero. That is, you're dividing actual revenues by forecasts that are zero.

Although this seems like a fairly benign issue, in a more complex, multilayered analytical process, these errors could compromise the integrity of your data analysis. To avoid these errors, you can perform a conditional analysis on your dataset using the IIf function, evaluating the Forecast field for each record before performing the calculation. If the forecast is zero, you'll bypass the calculation and simply return a value of zero. If the forecast is not zero, you'll perform the calculation to get the correct value. The IIf function would look like this:

IIf([Forecast]=0,0,[Actual]/[Forecast])

Figure 13.11 demonstrates how this IIf function is put into action.

As you can see in Figure 13.12, the errors have been avoided.

Saving time with IIf

You can also use the IIf function to save steps in your analytical processes and, ultimately, save time. For example, imagine that you need to tag customers in a lead list as either large customers or small customers, based on their dollar potential. You decide that you'll update the MyTest field in your dataset with “LARGE” or “SMALL” based on the revenue potential of the customer.

Without the IIf function, you would have to run the two update queries shown in Figures 13.13 and 13.14 to accomplish this task.

Will the queries in Figures 13.13 and 13.14 do the job? Yes. However, you could accomplish the same task with one query using the IIf function.

The update query shown in Figure 13.15 illustrates how you can use an IIf function as the update expression.

Take a moment and look at the IIf function being used as the update expression.

IIf([DollarPotential]>=10000,"LARGE","SMALL")

This function tells Access to evaluate the DollarPotential field of each record. If the DollarPotential field is greater than or equal to 10,000, use the word “LARGE” as the update value; if not, use the word “SMALL.”

Nesting IIf functions for multiple conditions

Sometimes the condition you need to test for is too complex to be handled by a basic IF…THEN…ELSE structure. In such cases, you can use nested IIf functions—that is, IIf functions that are embedded in other IIf functions. Consider the following example:

IIf([VALUE]>100,"A",IIf([VALUE]<100,"C","B"))

This function will check to see if VALUE is greater than 100. If it is, then "A" is returned; if not (else), a second IIf function is triggered. The second IIf function will check to see if VALUE is less than 100. If yes, then "C" is returned; if not (else), "B" is returned.

The idea here is that because an IIf function results in a true or false answer, you can expand your condition by setting the false expression to another IIf function instead of to a hard-coded value. This triggers another evaluation. There is no limit to the number of nested IIf functions you can use.

Using IIf functions to create crosstab analyses

Many seasoned analysts use the IIf function to create custom crosstab analyses in lieu of using a crosstab query. Among the many advantages of creating crosstab analyses without a crosstab query is the ability to categorize and group otherwise unrelated data items.

In the example shown in Figure 13.16, you're returning the number of account managers hired before and after 2014. Categorizations this specific would not be possible with a crosstab query.

The result, shown in Figure 13.17, is every bit as clean and user-friendly as the results would be from a crosstab query.

Another advantage of creating crosstab analyses without a crosstab query is the ability to include more than one calculation in your crosstab report. For example, Figure 13.18 illustrates a query where the sum of units and revenue will be returned in crosstab format.

As you can see in Figure 13.19, the resulting dataset provides a great deal of information in an easy-to-read format. Because a standard crosstab query does not allow more than one value calculation (in this case, units and revenue are values), this particular view would not be possible with a standard crosstab query.