Section 8.5. Tutorial: Flagging Out-of-Stock Orders

8.5. Tutorial: Flagging Out-of-Stock Orders

Boutique Fudge has a challenge. It makes all its products in small batches, and products frequently sell out. For example, if its supply of imported durian dries up, then so too does its world-famous Mocha Malaysian Espresso Milk.

However, eager shoppers keep ordering products that aren't in stock. Eventually they'll get the goods, but an order for an out-of-stock product might linger, lonely and forgotten, in the database for weeks. Boutique Fudge would prevent a lot of customer confusion (not to mention thirst) if it could track down the folks who've ordered out-of-stock items and warn them about the wait.

The database designers at Boutique Fudge have thought about this problem, and have decided they want a field in the Orders table that lets them mark orders that are waiting due to out-of-stock ingredients . They decide to use a Yes/No field (Section 2.3.7) named OnHold. That way, when the warehouse workers are filling an order, they can save time by ignoring all the orders on hold. And the customer service department can track down the customers who placed these orders and explain the problem.

So far, there's nothing new in this example. But here's the trick: Boutique Fudge wants to automate the process of setting the OnHold field. It wants to be able to run a query that can look at the UnitsInStock field in the Products table and then set the OnHold field for any in-progress orders that include an out-of-stock item. Now that you've mastered action queries, you're ready to consider this mindbending puzzle.

Like many problems in Access, you can solve this challenge by attacking it one piece at a time. Here, you'll solve the problem by creating two separate queries:

• A select query that finds orders containing out-of-stock products

• An action query that updates the OnHold field for the out-of-stock items

8.5.1. Finding Out-of-Stock Items

The first step is finding all the orders that include out-of-stock items. To do this, you need a query that includes two tables:

• Products , because it contains the fields with the stock levels

• OrderDetails , because it tells you which orders include a specific product

In this case, the OrderDetails table's the child table, and the Products table's the parent. (See Section 5.4.2 if you need a refresher on how the Boutique Fudge database is structured.) As a result, when you perform this query, you're really getting a list of OrderDetails records, supplemented with the product information.

Once you've created a query with the right tables, you need to add the appropriate fields:

• UnitsInStock (from the Products table). This field tells you if the product's out of stock. To find just the out-of-stock order items, set the Criteria box to 0.

• OrderID (from the OrderDetails) table. This field identifies the orders that are affected.

There's still one problem. When you run this query, you may see the same order ID appear multiple times. That's because you're retrieving a list of out-of-stock order items , and there might be several out-of-stock items in the same order. (You certainly don't want customer service calling shoppers multiple times, telling them their orders are delayed, right?) The easiest way to fix this problem is to tell Access to ignore duplicates in your query by following these steps:

1. Choose Query Tools Design Show/Hide Property Sheet .

   A Property Sheet box appears on the right side of the Access window, with lowlevel query settings.

2. Click somewhere on the empty space on the query design surface (like just beside one of the table boxes) .

   The Property Sheet box should say "Selection Type: Query Properties" at the top.

3. In the Property Sheet box, change the Unique Values setting from No to Yes .

   Now each order ID appears only once in the query results.

Figure 8-9 shows the completed query.

Figure 8-9. This query (named OrdersWithOutOfStockItems) generates a list of out-of-stock product IDs. Notice that it uses the UnitsInStock field for filtering, but it doesn't include it in the results (the Show box, circled, isn't turned on). To avoid having the same OrderID appear more than once (if it contains more than one out-of-stock item), the Unique Values query property (also circled) is set to Yes.

8.5.2. Putting the Orders on Hold

Next, you need to perform an update query that modifies all the problematic orders. This query needs to find all the order records found by the OrdersWith-OutOfStockItems query and then change them.

The solution? An update query, of course, as described in Section 8.1.2. In your update query, you need to use the Orders table and two fields:

• ID . You use this field to find the order records you want to update.

• OnHold . You update this field to Yes to place the order on hold.

You already know enough to add both these fields to the query, and to fill in the Update To box for the OnHold field (with the value Yes). However, the tricky part's finding just the right records. Clearly, you need to find orders that have one of the ID values you pinpointed in the OrdersWithOutOfStockItems query. But how can you use that query inside your update query?

To pull this off, you need a couple of new tricks in your filter expression. First, you need to use the In keyword, which checks to see if a value falls somewhere within a list of values. Here's an example of the In keyword at work:

 In (14,15,18) 

This filter expression matches any records that have ID values of 14, 15, or 16.

Obviously, it's way too much work to type in all the ID values by hand. It makes more sense to reuse the work you did when you created the OrdersWithOutOfStockItems. To make this process happen, you need to use another fancy move: a subquery .

A subquery's a query that's embedded inside another query. When you write your subquery, you need to use the SQL syntax you learned about in Section 6.2.3. You start with the word SELECT, list the fields you want to get, followed by the word FROM, and finish things off with the name of the table or query you're using. Here's the SQL for a select query that gets all the order IDs from the OrdersWithOutOfStockItems query:

 SELECT OrderID FROM OrdersWithOutOfStockItems 

Now that you have both the ingredients you need, you just need to fuse them together into one super-elegant filter expression. Here's the final product:

 In (SELECT OrderID FROM OrdersWithOutOfStockItems) 

You place this filter expression in the ID field. It gets all the IDs for problematic orders using the OrdersWithOutOfStockItems query, and then compares that against the full list of records in the Orders table. The completed action query's shown in Figure 8-10.

Figure 8-10. This update query (PutOutOfStockOrdersOnHold) ensures that Boutique Fudge will have happy customers for years to come. It selects the orders that have out-of-stock items, and then applies the change to the OnHold field. Now you just need to make sure that the customer service reps are polite.

If you create a query like this one, you'll probably want to include another query that does the reverse, and puts on-hold queries back into action, provided that stock's available. Based on what you've learned in this section, you shouldn't have any problem crafting the query you need.