Section 6.1. Query Basics

6.1. Query Basics

As the name suggests, queries are a way to ask questions about your data, like what products net the most cash, where do most customers live, and who ordered the embroidered toothbrush? Access saves each query in your database, like any other database object (Section 1.2). Once you've saved a query, you can run it any time you want to take a look at the live data that meets your criteria.

Queries' key feature is their amazing ability to reuse your hard work. Queries also introduce some new features that you don't have with the datasheet alone:

• Queries can combine related tables . This feature's insanely useful because it lets you craft searches that take related data into account. In the Boutique Fudge example, you can use this feature to create queries that find orders with specific product items, or orders made by customers living in specific cities. Both these searches need relationships, because they branch out past the Orders table to take in information from other tables (like Products and Customers). You'll see how this works in Section 6.3.

• Queries can perform calculations . The Products table in the Boutique Fudge database lists price information, along with the quantity in stock. A query can multiply these details, and then add a column that lists the calculated value of the product you have on hand. You'll try out this trick in Chapter 7 (Section 7.1).

• Queries can perform summaries . To analyze large chunks of data, you can group together rows with similar information. You can group all the orders by customer to find out who's spending the most. Or you can group orders by products to have a quick line-by-line list that compares the sales of Thermo-Nutcular Fudge against Vanilla Bean Dream. You'll learn this technique in Chapter 7 (Section 7.3).

• Queries can automatically apply changes . If you want to find all the orders made by a specific person and reduce the cost of each one by 10 percent, then a query can apply the entire batch of changes in one step. This action requires a different type of query, an action query , which you'll consider in Chapter 8.

In this chapter, you'll consider the simplest and most common type of query: the select query , which retrieves a subset of information from a table. Once you've retrieved this information, you print or edit it using a datasheet, in the same way you interact with a table.