Chapter 6. Queries That Select Records

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 your database. Even though the Boutique Fudge company has only one Orders table, it may have dozens (or more) queries, each with different sorting and filtering options. If you want to find the most expensive orders, then you don't need to apply the filtering, sorting, and column hiding settings by handinstead, you can just fire up the MostExpensiveOrdersLastMonth query, which pulls out just the information you need. Similarly, if you want to find the fudge-a-holics, then you can run the LargeRepeatFudgeOrders query.

Queries are a staple of database design. In this chapter, you'll learn all you need to design and fine-tune basic queries.