Creating Queries That Make Decisions


Besides the usual tasks of sorting, filtering, and calculating, queries are also useful for analyzing data. For example, the query from the previous section returned the number of units left in stock. If you see a negative number returned, obviously a product is going to be backordered, so you need to contact the supplier right away. However, some products also have a specified reorder level, which is the minimum number of units that need to be in stock before the product is reordered. So rather than waiting for the stock to get to 0 (or less), you might reorder when it gets down to 25 units or 10 units, for instance.

A logical approach here would be to add the ReorderLevel field to the query and then compare the Units Left In Stock calculation with the reorder level. If the number of units remaining is less than or equal to the reorder level, you need to reorder the product; otherwise, you do nothing.

This approach will work, but it suffers from two drawbacks:

  • It can be tedious and time-consuming if you have a lot of products.

  • It's easy to make a mistake one way or the other (that is, to reorder a product that has sufficient stock or to miss reordering a product that is below the reorder threshold).

Making Decisions with the IIf Function

The solution to both problems is to get Access to make the decision for you. One of the secrets to this is a very handy function called IIf (which you read as "Inline If"). Here's the syntax:

IIf(logical_test, value_if_true, value_if_false)

logical_test

A logical expression; that is, an expression that returns true or False.

value_if_true

The value returned by the function if logical_test evaluates to true.

value_if_false

The value returned by the function if logical_test evaluates to False.


Let's start with a simple example:

 IIf([UnitsInStock]) = 0, "Reorder", "Don't reorder") 

The logical test is the expression [UnitsInStock] = 0. If this returns true, the function returns the string value "Reorder"; otherwise, it returns the string value "Don't reorder". The idea is that you use this function as the expression for a calculated field, as shown in Figure 4.17. This creates a field that, when you run the query, displays either "Reorder" or "Don't reorder" for each product, as shown in Figure 4.18.

Figure 4.17. This query uses the IIf function to test whether the UnitsInStock field is 0.


Figure 4.18. The dynaset produced by the query in Figure 4.17.


Determining Whether Stock Needs to Be Reordered

A more realistic example takes into account the day's orders by subtracting them from the current stock and then comparing the result with the product's reorder level:

 IIf([UnitsInStock] - Sum([Quantity]) <= [ReorderLevel], "Yes", "No") 

If the units left in stock are less than or equal to the reorder level, the function returns the string "Yes"; otherwise, it returns "No". As you can see in Figure 4.19, I've used this expression as the basis for the calculated Reorder field, and I've also set the field's criteria to "Yes" so that the query returns the records for only those products that need ordering, as shown in Figure 4.20.

Figure 4.19. This query uses the IIf function to test whether the number of units left in stock is at or below the reorder level.


Figure 4.20. The dynaset produced by the query in Figure 4.19.


Making Decisions with the Switch Function

The IIf function is certainly very useful, but it's limited by the fact that it can perform only a single logical text. You can get around that to a certain extent by using the And or Or operators. For example, suppose an order qualifies for a bonus discount only if the total quantity is at least 50 units and the order total is at least $1,000. Here's a simplified version of the required logical test:

 If Quantity >= 50 And Total >= 1000 

Here's an actual IIf function that tests for this:

 IIf(Sum([Quantity]) >= 50 And Sum([UnitPrice] * [Quantity] * (1 - [Discount])) >= 1000, 0.05, 0) 

Similarly, suppose an order qualifies for a bonus discount only if the total quantity is at least 50 units or the order total is at least $1,000. Here's an IIf function that tests for this:

 IIf(Sum([Quantity]) >= 50 Or Sum([UnitPrice] * [Quantity] * (1 - [Discount])) >= 1000, 0.05, 0) 

This is a powerful idea, but you're still performing only a single logical test and then returning one of two values depending on whether the result is true or False.

For more complex situations, you need a tool that can run multiple tests and return multiple values depending on the result. Fortunately, Access comes with just the thingthe Switch function:

Switch(test1, value1 [, test2, value2, ...])

test1

A logical expression; that is, an expression that returns true or False.

value1

The value returned by the function if test1 evaluates to true.

test2

A logical expression; that is, an expression that returns true or False.

value2

The value returned by the function if test2 evaluates to TRue.


For example, Northwind's Shippers table lists three shipping companies: Speedy Express (ShipperID = 1), United Package (ShipperID = 2), and Federal Shipping (ShipperID = 3). Suppose that each company charges based on the total value of an order, and the charges are 5%, 10%, and 15%, respectively. The freight charge formula will look something like this:

 Total * Shipping Charge 

Calculating the Shipping Charge portion is a perfect task for the Switch function:

 Switch([ShipperID] = 1, .05, [ShipperID] = 2, .1, [ShipperID] = 3, .15) 

If ShipperID is 1 (Speedy Express), the function returns .05; if ShipperID is 2 (United Package), the function returns .1; if ShipperID is 3 (Federal Shipping), the function returns .15.



Tricks of the Microsoft Office Gurus
Tricks of the Microsoft Office Gurus
ISBN: 0789733692
EAN: 2147483647
Year: 2003
Pages: 129

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net