In a typical database, with thousands or millions of records, you may find it quite a chore finding the information you need. In Chapter 28, 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 mailorder 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 hand—instead, 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 to design and fine-tune 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 can print or edit it using a datasheet, in the same way you interact with a table.
In addition to using queries to retrieve data, you can also use them to change data. Queries that take this more drastic step—whether it’s deleting, updating, or adding records—are known collectively as action queries. You’ll get a quick introduction to action queries at the end of this chapter.
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 (Understanding Access Databases). 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).
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.
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 learn about later in this chapter.
Access gives you three ways to create a query:
The Query wizard gives you a quick-and-dirty way to build a simple query. However, this option also gives you the least control.
Design view offers the most common approach to query building. It provides a handy graphical tool that you can use to perfect any query.
SQL view gives you a behind-the-scenes look at the actual query command, which is a piece of text (ranging from one line to more than a dozen) that tells Access exactly what to do. The SQL view’s where many Access experts hang out; for more information on the world of SQL, see Access 2007: The Missing Manual.
The best starting point for query creation’s the Design view. The following steps show you how it works. (To try this out yourself, you can use the BoutiqueFudge. accdb database that’s included with the downloadable samples for this chapter.) The final result—a query that gets the results that fall in the first quarter of 2007—is shown in Figure 31-6.
Here’s what you need to do:
Choose Create → Other → Query Design.
A new design window appears, where you can craft your query. But before you get started, Access pops open the Show Table dialog box, where you can choose the tables that you want to work with (Figure 31-1).
Figure 31-1. You’ve seen the Show Table dialog box before—it’s the same way you added tables to the relationships window in Chapter 30.
Select the table that has the data you want, and then click Add (or just doubleclick the table).
In the Boutique Fudge example, you need the Orders table.
Access adds a box that represents the table to the design window. You can repeat this step to add several related tables, but for now stick with just one.
Click Close.
The Show Table dialog disappears, giving you access to the Design view for the query.
Select the fields you want to include in your query.
To select a field, double-click it in the table box (Figure 31-2). Take care not to add the same field more than once, or that column shows up twice in the results. If you’re using the Boutique Fudge example, then make sure you choose at least the ID, DatePlaced, and CustomerID fields.
Figure 31-2. Each time you double-click a field in the table box, Access adds it to the field list at the bottom of the window. You can then configure various settings to control filtering criteria and sorting for that column. If you don’t want to keep mousing back to the table box, then you can add a field directly to the column list by choosing its name from the drop-down Field box.
You can double-click the asterisk (*) to choose to include all the columns from a table. However, in most cases, it’s better to add each column separately. Not only does this help you more easily see at a glance what’s in your query, it also lets you choose the column order, and use the field for sorting and filtering.
Arrange the fields from left to right in the order you want them to appear in the query results.
When you run the query, the columns appear in the same order as they’re listed in the column list in Design view. (Ordinarily, this system means the columns appear from left to right in the order you added them.) If you want to change the order, then all you need to do is drag (as shown in Figure 31-3).
Figure 31-3. To reorder your columns, drag the gray bar at the top of the column you want to move to its new home. This technique’s similar to the technique you use to arrange columns in the datasheet (Rearranging Columns). In this example, the DatePlaced field’s being moved to the far left side.
If you want to hide one or more columns, then clear the Show checkbox for those columns.
Ordinarily, Access shows every column you’ve added to the column list. However, in some situations you want to work with a column in your query, but not actually display its data. Usually, it’s because you want to use the column values for sorting or filtering.
Choose a sort order.
If you don’t supply a sort order, then you’ll get the records right from the database in whatever order they happen to be. This convention usually (but not always) means the oldest records appear first, at the top of the table. To sort your table explicitly, choose the field you want to use to sort the results, and then, in the corresponding Sort box, choose a sorting option. In the current example, the table’s sorted by date in descending order, so that the most recent orders are first in the list (Figure 31-4).
Figure 31-4. Choose Ascending if you want to sort a text field from A-Z, a numeric field from lowest to highest, or a date field from oldest to most recent. Choose Descending to use the reverse order. Sorting has more information about sorting and how it applies to different data types.
Set your filtering criteria.
Filtering (Quick filters) is a tool that lets you focus on the records that interest you and ignore all the rest. Filtering cuts a large swath of data down to the information you need, and it’s the heart of many a query. (You’ll learn much more about building a filter expression in the next section.)
Once you have the filter expression you need, place it into the Criteria box for the appropriate field (Figure 31-5). In the current example, you can put this filter expression in the Criteria box for the DatePlaced field to get the orders placed in the first three months of the year:
>=#1/1/2007# And <=#3/31/2007# You aren’t limited to a single filter—in fact, you can add a separate filter expression to each field. If you want to use a field for filtering but not display it in the results, then clear the Show checkbox for that field.
Choose Query Tools | Design → Results → Run.
Now that you’ve finished the query, you’re ready to put it into action. When you run the query, you’ll see the results presented in a datasheet (complete with lookups on linked fields), just like when you edit a table. (Figure 31-6 shows the result of the query on the Orders table.)
You can switch back to Design view by right-clicking the tab title and then choosing Design View.
Figure 31-5. Here’s a filter that finds orders made in a date range (from January 1 to March 31, in the year 2007). Notice that when you use an actual hard-coded date as part of a condition (like January 1, 2007 in this example), you need to bracket the date with the # symbols. For a refresher about date syntax, refer to Validating numbers.
Figure 31-6. Here are the results of a query that shows orders placed within a specific date range. You can use the datasheet window to review or print your results, or you can edit information just as you would in a table datasheet.
Note
The datasheet for your query acquires any formatting you applied to the datasheet of the underlying table. If you applied a hot-pink background and cursive font to the datasheet for the Orders table, then the same settings apply to any queries that use the Orders table. However, you can change the datasheet formatting for your query just as you would with a table.
Save the query.
You can save your query at any time using the keyboard shortcut Ctrl+S. If you don’t, then Access automatically saves your query when you close the query tab (or your entire database). Of course, you don’t need to save your query. Sometimes you might create a query for a specific, one-time-only task. If you don’t plan to reuse the query, then there’s no point in cluttering up your database with extra objects.
The first time you save your query, Access asks for a name. Use the same naming rules that you follow for tables—refrain from using spaces or special characters, and capitalize the first letter in each word. A good query describes the view of data that it presents. One good choice for the example shown in Figure 31-6 is FirstQuarterOrders_2007.
Note
Remember, when you save a query, you aren’t saving the query results—you’re just saving the query design, with all its settings. That way, you can run the query any time to get the live results that match your criteria.
Once you’ve created a query, you’ll see it in your database’s navigation pane (Figure 31-7). If you’re using the standard All Tables view, then the query appears under the table that it uses. If a query uses more than one table, then the same query appears in more than one group in the navigation pane.
Figure 31-7. By default, the navigation pane organizes your queries so they appear right underneath the table they use. For example, the TopProducts query (shown here) appears under the Products table.
You can launch the query at any time by double-clicking it. Suppose you’ve created a query named TopProducts that grabs all the expensive products in the Products table (using the filter criteria >50 on the Price field). Every time you need to review, print, or edit information about expensive products, you run the TopProducts query. To fine-tune the query settings, right-click it in the navigation pane, and then choose Design View.
Access lets you open your table and any queries that use it at the same time. (They all appear in separate tabs.) However, you can’t modify the design of your table until you close all the queries that use it.
If you add new records to a table while a query’s open, then the new records don’t automatically appear in the query. Instead, you’ll need to run your query again. The quickest way is to choose Home → Records → Refresh → Refresh All. You can also close your query and open it again, because Access runs your query every time you open it in Datasheet view.
Note
Remember, a query’s a view of some of the data in your table. When you edit your query results, Access changes the data in the underlying table. On the other hand, it’s perfectly safe to rename, modify, and delete queries—after all, they’re there to make your life simpler.
The secret to a good query’s getting the information you want, and nothing more. To tell Access what records it should get (and which ones it should ignore), you need a filter expression.
The filter expression defines the records you’re interested in. If you want to find all the orders that were placed by a customer with the ID 1032, you could use this filter expression:
| =1032 |
To put this filter expression into action, you need to put it in the Criteria box under the CustomerID field.
Technically, you could just write 1032 instead of =1032, but it’s better to stick to the second form, because that’s the pattern you’ll use for more advanced filter expressions. It starts with the operator (in this case, the equals sign) that defines how Access should compare the information, followed by the value (in this case, 1032) you want to use to make the comparison.
If you’re matching text, then you need to include quotation marks around your value. Otherwise, Access wonders where the text starts and stops:
| ="Harrington Red” |
Instead of using an exact match, you can use a range. Add this filter expression to the OrderTotal field to find all the orders worth between $10 and $50:
| <50 And >10 |
This condition’s actually two conditions (less than 50 and greater than 10), which are yoked together by the powerful And keyword (Combining validation conditions).
Date expressions are particularly useful. Just remember to bracket any hardcoded dates with the # character (Validating numbers). If you add this filter condition to the DatePlaced field, then it finds all the orders that were placed in 2007:
| <#1/1/2008# And >#12/31/2006# |
This expression works by requiring that dates are earlier than January 1, 2008, but later than December 31, 2006.
Tip
With a little more work, you could craft a filter expression that gets the orders from the first three months of the current year, no matter what year it is. This trick requires the use of the functions Access provides for dates. See Setting Default Values for more details.
When you run an ordinary query, you see all the results that match your filter conditions. If that’s more than you bargained for, you can use filter expressions to cut down the list.
However, in some cases, filters are a bit more work than they should be. Imagine a situation where you want to see the top 10 most expensive products. Using a filter condition, you can easily get the products that have prices above a certain threshold. Using sorting, you can arrange the results so the most expensive items turn up at the top. However, you can’t as easily tell Access to get just 10 records and then stop.
In this situation, the query Design view has a shortcut that can help you out. Here’s how it works:
Open your query in Design view (or create a new query and add the fields you want to use).
This example uses the Products table, and includes the ProductName and Price fields.
Sort your table so that the records you’re most interested in are at the top.
If you want to find the most expensive products, then add a descending sort (Sorting) on the Price field.
In the Query Tools | Design → Query Setup → Return box, choose a different option (Figure 31-8).
The standard option’s All, which gets all the matching records. However, you can choose 5, 25, or 100 to get the top 5, 25, or 100 matching records, respectively. Or, you can use a percentage value like 25 percent to get the top quarter of matching records.
Figure 31-8. If you don’t see the number you want in the list, just type it into the Return box on your own. There’s no reason you can’t grab the top 27 most expensive products.
Note
For the Query Tools | Design → Query Setup → Return box to work, you must choose the right sort order. To understand why, you need to know a little more about how this feature works. If you tell Access to get just five records, it actually performs the normal query, gets all the records, and arranges them according to your sort order. It then throws everything away except for the first five records in the list. If you’ve sorted your list so that the most expensive products are first (as in this example), you’re left with the top five budget-busting products in your results.
Run your query to see the results (Figure 31-9).
Design view’s usually the best place to start constructing queries, but it’s not the only option. You can use the Query wizard to give you an initial boost, and then refine your query in Design view.
The Query wizard works by asking you a series of questions, and then creating the query that fits the bill. Unlike many of the other wizards in Access and other Office applications, the Query wizard’s relatively feeble. It’s a good starting point for query newbies, but not an end-to-end performer.
Here’s how you can put the Query wizard to work:
Choose Create → Other → Query Wizard.
Access gives you a choice of several different wizards (Figure 31-10).
Choose a query type. The Simple Query wizard’s the best starting point for now.
The Query wizard includes a few common kinds of queries. With the exception of the crosstab query, there’s nothing really unique about any of these choices. You’ll learn to create them all using Design view:
Simple Query Wizard gets you started with an ordinary query, which displays a subset of data from a table. This query’s the kind you created in the previous section.
Crosstab Query Wizard generates a crosstab query, which lets you summarize large amounts of data using different calculations. You can find more on this advanced topic in Access 2007: The Missing Manual.
Find Duplicates Query Wizard is similar to the Simple Query wizard, except it adds a filter expression that shows only records that share duplicated values. If you forgot to set a primary key or create a unique index for your table (Preventing Duplicate Values with Indexes), then this can help you clean up the mess.
Find Unmatched Query Wizard is similar to the Simple Query wizard, except it adds a filter expression that finds unlinked records in related tables. You could use this to find an order that isn’t associated with any particular customer.
Click OK.
The first step of the Query wizard appears.
In the Tables/Queries box, choose the table that has the data you want. Then, add the fields you want to see in the query results, as shown in Figure 31-11.
For the best control, add the fields one at a time. Add them in the order you want them to appear in the query results, from left to right.
You can add fields from more than one table. To do so, start by choosing one of the tables, add the fields you want, and then choose the second table and repeat the process. This process really makes sense only if the tables are related.
Figure 31-11. To add a field, select it in the Available Fields list, and then click the > arrow button (or just double-click it). You can add all fields at once by clicking the >> arrow button, and you can remove fields by selecting them in the Selected Fields list and then clicking <. In this example, three fields are included in the query.
Click Next.
If your query includes a numeric field, the Query wizard gives you the choice of creating a summary query that arranges rows into groups, and calculates information like totals and averages. If you get this choice, pick Detail and then click Next.
The final step of the Query wizard appears (Figure 31-12).
Supply a query name in the “What title do you want for your query?” box.
If you want to fine-tune your query, then choose “Modify the query design”. If you’re happy with what you’ve got, then choose “Open the query to view information” to run the query.
One reason you may want to open your query in Design view is to add filter conditions (Quick filters) to pick out specific rows. Unfortunately, you can’t set filter conditions in the Query wizard.
Click Finish.
Your query opens in Design view or Datasheet view, depending on the choice you made in step 7. You can run it by choosing Query Tools | Design → Results → Run.
Action queries aren’t quite as useful as select queries, because they tend to be less flexible. You create an ideal query once, and reuse it over and over. Select queries fit the bill, because you’ll often want to review the same sort of information (last week’s orders, top-selling products, class sizes, and so on). But action queries are trickier, because they make permanent changes.
In most cases, a change is a one-time-only affair, so you don’t have any reason to hang onto an action query that just applies the same change all over again. And even if you do need to modify some details regularly (like product prices or warehouse stocking levels), the actual values you set aren’t the same each time. As a result, you can’t create an action query that can apply your change in an automated fashion.
But before you skip this chapter for greener pastures, it’s important to consider some cases where action queries are surprisingly handy. Action queries shine if you have:
Batch tasks that you want to repeatedly apply. Some tasks can be repeated exactly. You may need to copy a large number of records from one table to another, delete a batch of old information, or update a status field across a group of records. If you need to perform this kind of task over and over again, action queries are a perfect timesaver.
Complex or tedious tasks that affect a large number of records. Every once in a while, a table needs a minor realignment. You may decide that it’s time to increase selling prices by 15 percent, or you may discover that all orders linked to customer 403 really should point to customer 404. These are one-off tasks, but they affect a large number of records. To polish them off, you need to spend some serious time in the datasheet—or you can craft a new action query that makes the change more efficiently. When you’re done, you decide whether you delete the action query, or save it in case you want to modify and reuse your work later on.
In the wrong hands, action queries are nothing but a high-tech way to shoot yourself in the foot. They commit changes (usually to multiple records), and once you’ve applied the changes, you can’t reverse them. Some database fans avoid action queries completely.
If you do decide to use action queries (and there are plenty of handy tricks you can accomplish with them), then you need to take the right precautions. Most importantly, before you use an action query, make a database backup! This step’s especially crucial when you’re creating a new action query, because it may not always generate the result you expect. To make a backup, you can copy your .accdb database file (just like you would any other file; one way is to right-click it, and then select Copy). If you don’t want to mess with Windows Explorer, then you can create a backup without leaving Access by selecting the Office button → Manage → Back Up Database (Making Backups).
Tip
It’s always easier to make a backup than to clean up the wake of changes left by a rampaging action query.
Backups are great for disaster recovery, but it’s still a good idea to avoid making a mistake in the first place. One safe approach is to start by creating a select query. You can then make sure your query’s selecting the correct records before taking the next step and converting it into an action query (by choosing one of the action query types in the Query Tools | Design → Query Type section of the ribbon).
Access has four types of action queries:
An update query changes the values in one or more records.
An append query selects one or more records, and then adds them to an existing table.
A make-table query selects one or more records, and then creates a new table for them.
A delete query deletes one or more records.
If this brief introduction to action queries has piqued your interest, grab a copy of Access 2007: The Missing Manual for the lowdown on how to create them.