Up until now, your analyses have been straightforward. You build a query, you add some criteria, you add a calculation, you save the query, then you run the query whenever you need to. What happens however, if the criteria that governs you analysis changes frequently, or if your analytical processes depend on meeting certain conditions? In these situations, you would use a conditional analysis; an analysis whose outcome depends on a pre-defined set of conditions. Barring VBA code, several tools and functions enable you to build conditional analyses, some of which are parameter queries, the IIf function, and the Switch function. In this chapter, you learn how these tools and functions can help you save time, organize your analytical processes, and enhance your analysis.
You will find that when building your analytical processes, it's often difficult to anticipate every single combination of criteria that may be needed. This is where parameter queries can help.
A parameter query is an interactive query that prompts you for criteria before the query runs. A parameter query is useful when you need to ask a query different questions using different criteria each time it runs. To get a firm understanding of how a parameter query can help you, build the query in Figure 6-1. With this query, you want to see the all purchase orders logged during the 200705-system period.
Although this query gives you what you need, the problem is that the criterion for system period is hard-coded as 200705. That means if you want to analyze revenue for a different period, you essentially have to rebuild the query. Using a parameter query allows you to create a conditional analysis; that is, an analysis based on variables you specify each time you run the query. To create a parameter query, simply replace the hard-coded criteria with text that you have enclosed in square brackets ([ ]), as shown in Figure 6-2.
![To create a parameter query, replace the hard-coded criteria with text enclosed in square brackets [].](http://imgdetail.ebookreading.net/business/42/9780470567012/9780470567012__the-excel-analysts__9780470567012__figs__0602.png)
Figure 6.2. To create a parameter query, replace the hard-coded criteria with text enclosed in square brackets [].
Running a parameter query forces the Enter Parameter Value dialog box to open and ask for a variable. Note that the text you typed inside the brackets of your parameter appears in the dialog box. At this point, you would simply enter your parameter, as shown in Figure 6-3.
When you run a parameter query, Access attempts to convert any text to a literal string by wrapping the text in quotes. However, if you place square brackets ([ ]) around the text, Access thinks that it is a variable and tries to bind some value to the variable using the following series of tests:
Access checks to see if the variable is a field name. If Access identifies the variable as a field name, that field is used in the expression.
If the variable is not a field name, Access checks to see if the variable is a calculated field. If Access determines the expression is indeed a calculated field, it simply carries out the mathematical operation.
If the variable is not a calculated field, Access checks to see if the variable is referencing an object such as a control on an open form or open report.
If all else fails, the only remaining option is to ask the user what the variable is, so Access displays the Enter Parameter Value dialog box, showing the text you entered in the Criteria row.
As with other functionality in Access, parameter queries come with their own set of ground rules that you should follow in order to use them properly.
You must place square brackets (
[ ]) around your parameter. If you do not, Access automatically converts your text into a literal string.You cannot use the name of a field as a parameter. If you do, Access simply replaces your parameter with the current value of the field.
You cannot use a period (
.), an exclamation mark (!), or square brackets ([]) in your parameter's prompt text.You must limit the number of characters in your parameter's prompt text. Entering parameter prompt text that is too long may result in your prompt being cut off in the Enter Parameter Value dialog box. Moreover, you should make your prompts as clear and concise as possible.
Tip
If you really want to use a field name in your parameter's prompt, you can follow the field name with other characters. For example, instead of using [Period], you could use [Period: ?]. As you read this, keep in mind that there is nothing magic about the colon (:) or the question mark (?). Any character will do. The idea is to allow Access to differentiate between your parameter and the field name while matching the original field name as closely as possible.
The example shown in Figure 6-2 uses a parameter to define a single criterion. Although this is the most common way to use a parameter in a query, there are many ways to exploit this functionality. In fact, it is safe to say that the more innovative you get with your parameter queries, the more elegant and advanced your impromptu analysis will be. This section covers some of the different ways you can use parameters in your queries.
You are not in any way limited in the number of parameters you can use in your query. Figure 6-4, on the other hand, demonstrates how you can utilize more than one parameter in a query. When you run this query, it prompts you 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.
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 here in Figure 6-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. Because three parameter prompts are built into this query, it prompts you for inputs three times: once for a starting period, once for an ending period, and once for a dollar amount. The number of returned records depends on the parameters you input. For instance, if you input 200701 as the starting period, 200703 as the ending period, and 5000 as the dollar amount, you get 1700 records.
One of the problems with a parameter query is that if the parameter is ignored when the query runs, the query returns no records. One way to get around this problem is to combine your parameter with a wildcard so that if the parameter is ignored, all records are returned. To demonstrate how you can use a wildcard with a parameter, build the query shown here in Figure 6-6. When you run this query, it will prompt you for a period. Because you are 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.
Figure 6.6. If the parameter in this query is ignored, the query returns all records, thanks to the wildcard (*).
Tip
Using the wildcard with a parameter also allows users to enter in a partial parameter and still get results. Suppose, for example, that the criteria in your parameter query is:
Like [Enter Lastname] & "*"
Entering A as the parameter would return all last names that start with the letter A.
Or, suppose the criteria in your parameter query is:
Like "*" & [Enter Lastname] & "*"
Entering A would return all last names that contain the letter A.
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 6-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 are 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.
You can also use parameters as arguments within functions. Figure 6-8 demonstrates the use of the DateDiff function employing parameters instead of hard-coded dates. When this query runs, it prompts you for a start date and anend date. The DateDiff function then uses these dates as arguments. Again, this allows you to specify new dates each time you run the query without ever having to rebuild the query.
Note
You will notice that when you run the query in Figure 6-8, you will only have to enter the Start date and the End date one time although they are both used in two places in the query. Once you assign a variable to a parameter, that assignment persists to every future instance of that parameter.
This is also a good time to note that the values you enter into your parameters must fit into the data type required for the function's argument. For example, if you are using a parameter in a date-oriented function (like DateDiff), the variable you enter into that parameter must be a date or the function won't work.
Figure 6.7. You can use parameters in calculations, enabling you to change the calculations variables each time you run the query.
TRICKS OF THE TRADE: CREATING A PARAMETER PROMPT THAT ACCEPTS MULTIPLE ENTRIES
The parameter query in Figure 6-9 enables you to dynamically filter results by a variable period that you specify within the parameter. However, this query does not allow you to see results for more than one period at a time.
You could use more than one parameter, as shown in the following figure. Unlike the query in Figure 6-9, this query allows you to include more than one period in your query results. However, you are still limited to the number of parameters built into the query (in this case, three) as shown in Figure 6-10.
Figure 6.10. This query enables you to filter by three periods at a time instead of one. But what if you need to filter more than three periods?
So how do you allow for any number of parameter entries? The answer is relatively easy. You create a parameter that is passed through an InStr function to test for a position number (feel free to revisit Chapter 4 to get a refresher on the InStr function).
The query shown in Figure 6-11 demonstrates how to do this.
Notice that the parameter is not being used as criteria for the Period field. Instead, it is being used in an Instr function to test for the position number of the variable you enter into the parameter prompt, as follows:
InStr([Enter Periods separated by commas],[Period])
If the Instr function finds your variable, it returns a position number; if not, it returns 0. Therefore, you only want records that returned a position number greater than zero (hence, the criteria for the parameter).
When you run this query, Access displays the standard Enter Parameter Value dialog box (see Figure 6-12). You can then type in as many variables as you want.
Parameter queries are not the only tools in Access that allow for conditional analysis. Access also has built-in functions that facilitate value comparisons, data validation, and conditional evaluation. Two of these functions are theIIf function and the Switch function. These conditional functions (also called program flow functions) are designed to test for conditions and provide different outcomes based on the results of those tests. In this section, you learn how to control the flow of your analysis by utilizing the IIf and Switch functions.
The IIf (immediate if) function replicates the functionality of an IF statement for a single operation. The IIf function evaluates a specific condition and returns a result based on a True or False determination.
Tip
Think of the commas in an IIf function as THEN and ELSE statements. Consider the following IIf function, for instance:
IIf(Babies = 2, "Twins", "Not Twins")
This function literally translates to: If Babies equals 2, then Twins, else Not Twins.
To demonstrate a simple problem where the IIf function comes in handy, build the query shown in Figure 6-13.
When you run the query, you'll notice that not all the results are clean. As you can see in Figure 6-14, you are getting some errors due to division by zero. That is to say, you are dividing actual revenues by forecasts that are zero.
Figure 6.13. This query performs a calculation on the Actual and the Forecast fields to calculate a percent to forecast.
Figure 6.14. The errors shown in the results are due to the fact that some revenues are being divided by zeros.
Although this seems like a 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 bypass the calculation and simply return a value of zero. If the forecast is not zero, you perform the calculation to get the correct value. The IIf function would look like this:
IIf([Forecast]=0,0,[Actual]/[Forecast])
Figure 6-15 demonstrates how this IIf function is put into action.
As you can see in Figure 6-16, the errors have been avoided.
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-sized customers or a small-sized customers, based on their dollar potential and you decide that you will 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 6-17 and 6-18 to accomplish this task.
Will the queries in Figures 6-17 and 6-18 do the job? Yes. However, you could accomplish the same task with one query using the IIf function.
The update query shown in Figure 6-19 illustrates how you can use an IIf function as the update expression.
Take a moment and look at the IIf function used as the update expression.
IIf([DollarPotential]>=10000,"LARGE","SMALL")
Figure 6.17. This query will update the MyTest field to tag all customers that have a revenue potential at or above 10,000 dollars with the word "LARGE."
Figure 6.18. This query updates the MyTest field to tag all customers that have a revenue potential less than 10,000 dollars with the word "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."
Tip
You can use conditional operators (AND, OR, BETWEEN) within your IIf functions to add a layers to your condition expression. For example, the following function tests for a dollar potential and segment to get a True or a False value.
IIf([DollarPotential]>10000 And [Segment]="Metal Fabrication","True","False")
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 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 checks 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.
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 6-20, you are returning the number of account managers hired before and after 2009. Categorizations this specific are not possible with a crosstab query.
The result, shown in Figure 6-21, is every bit as clean and user-friendly as the results would be from a crosstab query.
Figure 6.20. This query demonstrates how to create a crosstab analysis without using 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 6-22 illustrates a query where the sum of units and revenue are returned in crosstab format.
Figure 6.22. Creating crosstab-style reports using the IIf function allows you to calculate more than one value.
As you can see in Figure 6-23, 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 calculations (in this case units and revenue are values), this particular view is not possible with a standard crosstab query.
The Switch function enables you to evaluate a list of expressions and return the value associated with the expression determined to be True. To use the Switch function, you must provide a minimum of one expression and one value.
Although the IIf function is a versatile tool that can handle most conditional analysis, the fact is that the IIf function has a fixed number of arguments that limits it to a basic IF...THEN...ELSE structure. This limitation makes it difficult to evaluate complex conditions without using nested IIf functions. While there is nothing wrong with nesting IIF functions, they have the potential to be difficult to read and maintain.
To illustrate this point, consider this scenario: It is common practice to classify customers into groups based on annual revenue, or how much they spend with your company. Imagine that your organization has a policy of classifying customers into four groups: A, B, C, and D (see Table 6-1).
You have been asked to classify the customers in the TransactionMaster table, based on each customer's sales transactions. You can actually do this using either the IIf function or the Switch function.
Table 6.1. Customer Classifications
ANNUAL REVENUE | CUSTOMER CLASSIFICATION |
|---|---|
>= $10,000 | A |
>=5,000 but < $10,000 | B |
>=$1,000 but < $5,000 | C |
<$1,000 | D |
The problem with using the IIf function is that this situation calls for some hefty nesting. That is, you have to use IIf expressions within other IIf expressions to handle the easy layer of possible conditions. Here is how the expression would look if you opted to use the IIf function:
IIf([REV]>=10000,"A",IIf([REV]>=5000,"B",IIf([REV]>=1000,"C","D")))"
As you can see, this can get a bit convoluted. So much so, that the chances of making a syntax or logic error are high.
In contrast to the preceding nested IIf function, the following Switch function is rather straightforward:
Switch([REV]<1000,"D",[REV]<5000,"C",[REV]<10000,"B",True,"A")
This function tells Access that if REV is less than 1000, then return a value of "D". If REV is less than 5000, then return a value of "C". If REV is less than 10000, then return "B". If all else fails, use "A". Figure 6-24 demonstrates how you would use this function in a query.
Note
You may shrewdly notice that those records that are less than 1000 will also be less than 10,000. So why don't all the records get tagged with a value of "B"? Remember that the Switch function evaluates your expressions from left to right and only returns the value of the first expression that evaluates to True.
In this light, you want to sort the expressions in your Switch function accordingly, using an order that is conducive to the logic of your analysis.
Figure 6.24. Using the Switch function is sometimes more practical than using nested IIf functions. This query will classify customers by how much they spend.
When you run the query, you see the resulting dataset shown in Figure 6-25.
You will often need to perform analyses where specifications and circumstances for the analysis are variable. That is, the parameters and conditions for the analysis change each time you run it. In such cases, you need to perform a conditional analysis— an analysis whose outcome depends on a pre-defined set of conditions. Access has several built-in tools that enable conditional analyses; some of these are parameter queries, the IIf function, and the Switch function.
A parameter query is an interactive query that prompts you for criteria before the query runs. You often use this type of query when you need to pass different criteria each time the query runs. Running a parameter query forces the Enter Parameter Value dialog box to open and ask for a variable or criteria. You would simply enter your parameter, as shown in Figure 6-3.
Parameter queries are not the only tools in Access that allow for conditional analysis. Access also has built-in functions that facilitate value comparisons, data validation, and conditional evaluation. These conditional functions (also called program flow functions) are designed to test for conditions and provide different outcomes based on the results of those tests. Two of these functions are the IIf function and the Switch function.
The IIf (immediate if) function replicates the functionality of Excel's IF function, evaluating a specific condition as True or False. The IIf function saves steps in your analytical processes and, ultimately, saves time. The Switch function enables you to evaluate a list of expressions and return the value associated with the expression determined to be True. The power of the Switch function comes in evaluating multiple expressions at one time and determining which one is True.
Leveraging and employing conditional analysis is not only easy but helps save you time, organizes your analytical processes, and ultimately enhances your analysis.