Access 2010: The Missing Manual

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 (Yes/No) 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 ...