Access 2007: The Missing Manual

Chapter 6. Queries That Select Records

In a typical database, with thousands or millions of records, you may find it quite a chore finding the information you need. In Chapter 3, you learned how to go on the hunt using the tools of the datasheet, including filtering, searching, and sorting. At first glance, these tools seem like the perfect solution for digging up bits of hard-to-find information. However, there’s a problem: The datasheet features are temporary.

To understand the problem, imagine you’re creating an Access database for a mail-order food company named Boutique Fudge. Using datasheet filtering, sorting, and column hiding, you can pare down the Orders table so it shows only the most expensive orders placed in the past month. (This information’s perfect for targeting big spenders or crafting a hot marketing campaign.) Next, you can apply a different set of settings to find out which customers order more than five pounds of fudge every Sunday. (You could use this information for more detailed market research, or just pass it along to the Department of Health.) But every time you apply new datasheet settings, you lose your previous settings. If you want to jump back from one view to another, then you need to painstakingly reapply all your settings. If you’ve spent some time crafting the perfect view of your data, this process adds up to a lot of unnecessary extra work.

The solution to this problem’s to use queries: ready-made search routines that you store in ...