Ms Office Tune Up

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:

1. 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).

2. 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.

3. Click Close.

   The Show Table dialog disappears, giving you access to the Design view for the query.

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

   ---

5. 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.

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

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

   ---

8. 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.

9. 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.
   

   ---

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

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.

Popularity: 3% [?]