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, 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 your ...

Get Access 2013: The Missing Manual now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.