Section 6.2. Creating Queries | Access 2007 for Starters[c] The Missing Manual

6.2. Creating Queries

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.

Note: If you decide to use the Query wizard to create your query, then you'll probably want to refine your query later on using Design view.
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 .

6.2.1. Creating a Query in Design View

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 resulta query that gets the results that fall in the first quarter of 2007is shown in Figure 6-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 6-1).
Select the table that has the data you want, and then click Add (or just double-click 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 6-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 6-1. You've seen the Show Table dialog box beforeit's the same way you added tables to the relationships window in Chapter 5.

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.

Note: A good query includes only the fields you absolutely need. Keeping your query lean ensures it's easier to focus on the important information (and easier to fit your printout on a page).

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 6-3).

Figure 6-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.

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 6-4).

Figure 6-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 (Section 3.1.2). In this example, the DatePlaced field's being moved to the far left side.

Tip: You can sort based on several fields. The only trick's that your columns need to be ordered so that the first sorting criteria appears first (to the left) in the column list. Use the column rearranging trick from step 5 to make sure you've got it right.

Set your filtering criteria .

Filtering (Section 3.2.2) 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.)

Figure 6-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. Section 3.2.1 has more information about sorting and how it applies to different data types.

Once you have the filter expression you need, place it into the Criteria box for the appropriate field (Figure 6-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 filterin 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 6-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 6-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 Section 4.3.2.2.

Figure 6-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 tablesrefrain 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 6-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 6-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.

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.

Figure 6-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.

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 queriesafter all, they're there to make your life simpler.

6.2.1.1. Building filter expressions

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.

WORD TO THE WISE
Don't Get Confused by Lookups

As you know, lookups change the way values appear on the datasheet. If you add a lookup on the CustomerID field in the Orders table, then you don't see a cryptic number like 1032. Instead, you see some descriptive information, like the name Hancock, John .

However, when you write your filter expression, you need to remember what information's actually stored in the field. So the CustomerID filter expression =1032 works fine, but =Hancock, John doesn't, because the name information's actually stored separately. (It's in the Customers table, not the Orders table.)

Sometimes, you really do want to create a filter expression that uses linked information. You may want to find records in the Orders table using a customer name instead of a customer ID, because you don't have the ID value handy. In this situation, you have two choices:

You can look up the ID value you need in the Customers table before you start. Then, you can use that value when you build your query for the Orders table.
You can use a join query to get the name information from the Customers table, and display it alongside the rest of your order details. You'll learn how to perform this trick in Section 6.3.

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 (Section 4.3.2.4). Alternatively, you can use the Or keyword if you want to see results that meet any one of the conditions you've included (Section 4.3.2.4).

Date expressions are particularly useful. Just remember to bracket any hardcoded dates with the # character (Section 4.3.2.2). 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 Section 4.1.2 for more details.

UP TO SPEED
Filter Syntax

If filters seem uncannily familiar, there's a reason. Filters have exactly the same syntax as the validation rules you used to protect a table from bad data (Section 4.3). The only difference is the way Access interprets the condition. A validation rule like <50 And >10 tells Access a value shouldn't be allowed unless it falls in the desired range (10 to 50). But if you pop the same rule into a filter condition, it tells Access you aren't interested in seeing the record unless it fits the range. Thanks to this similarity, you can use all the validation rules you saw in Section 4.3.2, Section 4.3.2, Section 4.3.2.2, Section 4.3.2.3, Section 4.3.2.4, Section 4.3.3 to Section 4.4 as filter conditions.

6.2.1.2. Getting the top records

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 (Section 3.2.1) on the Price field.
In the Query Tools Design Query Setup Return box, choose a different option (Figure 6-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.

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 youve 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 6-9) .

Figure 6-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.

Figure 6-9. Here are the top five most expensive products.

6.2.2. Creating a Simple Query with the Query Wizard

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 6-10).

Figure 6-10. In the first step of the Query wizard, you choose from a small set of basic query types.
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 (Section 4.1.3), 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. You'll learn how this works in Section 6.3.2.1.
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 6-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. You'll learn more in Section 6.3.

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 6-12).

Figure 6-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.

Figure 6-12. In the last step, you choose the name for your query, and decide whether you want to see the results right away or refine it further in Design view.

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 (Section 3.2.2) 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.