Share this Page URL

Chapter 6. Sorting, Filtering, and Retri... > Designing Select Queries - Pg. 209

Sorting, Filtering, and Retrieving Data 209 8. This query shows budgets in comparison to the start dates of the campaigns, which is infor- mation that might be useful in determining the timing of expenses. Although you could gather this information by looking at the table's complete recordset, using a query creates a concise recordset, displaying only the data you need to review. Click the View button on the toolbar to switch back to Design view. The order in which you place fields in the query design grid is the default order of the fields in a form or report that's based on the query. Sorting is also affected by the order of the columns. If you're sorting on more than one field, the field you want to sort by first must be to the left of a secondary sort field. If you change the order of the fields in a query in Datasheet view (if you drag a column to a different location in the datasheet), the order of the fields in the query design grid is not af- fected. Also, a sort order that you apply to the results of a query in Datasheet view is not reflected in the query's design. In this example, the query's design specifies that records are sorted by LaunchDate. In Datasheet view, if you sort the query's results by Country, this order isn't saved in the query's design. Close qryBudgetReport. 9. Calculating the total for a field A quick way to add up the values in a field is to create a query that contains only that field. After adding the field, click View, Totals in the Query Design window. In the Totals row, select Sum. You'll see more examples of how to use the Totals row to summarize values later in this chapter. Just the Steps Create a simple select query 1. 2. 3. 4. 5. 6. In the Database window, click Queries, and then click New. In the New Query dialog box, select Design View, and then click OK. In the Show Tables dialog box, select the table (or tables) you need in the query, click Add, and then click Close. (You can also use the fields from another query in a query.) From the field lists, drag the fields you want in the query to the Field row in the query design grid. Select a sort order for the query. Click the Run button on the toolbar to see the query's results.