Access 2010: 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 is 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 and forth 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 is to use queries: readymade search routines that you store in ...