The Excel® Analyst's Guide to Access®

Chapter 6. Performing Conditional Analysis

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.

Using Parameter Queries

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.

Figure 6.1. This query has a hard-coded criterion for system ...