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:
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:
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:
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:
Figure 8-9 shows the completed query.
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:
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:
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.
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.