We are happy to announce that MOS 2013 Study Guide for Microsoft Access, by John Pierce, (ISBN 9780735669192) is now orderable! Please read on for ordering information and an excerpt from the book. To read the Contents at a glance and an excerpt from the introduction, check out our earlier blog post.
Order from O’Reilly click here
Order from Amazon click here
Order from Barnes & Noble click here
Order from Ada’s Technical Books and Café click here
After you set up a query, you can modify it by changing or rearranging the fields, showing and hiding query fields, and sorting the query’s results. You can also format the fields in a query. This section examines some of the ways you can modify a query.
With a query open in Design view, you can add, remove, and rearrange the query’s fields in several ways. To add fields, you can do the following:
To remove a field, click the top of the field’s column in the design grid, and then press Delete or click Delete Columns in the Query Setup group. You cannot use the Undo command to reverse this action.
The Query Setup group also contains options for inserting and deleting rows (when you need to define additional criteria, for example) and for opening the Expression Builder. If you want to change the order of the fields in the grid, click in the top of the column in the design grid (hold down the Shift key and click to select more than one adjacent column), and then drag the field or fields to the new position. Access displays a black bar to indicate the new position.
To add, remove, and rearrange fields in a query 1. In the Navigation pane, right-click the query, and then click Design View. 2. To insert a field, in the field list in the top pane of the Query Designer, select the field, and then drag the field to the query design grid. 3. To delete a field, click in the top of the field’s column, and then press Delete. 4. To change the order of the fields, click the top of the column for the field you want to move (hold down the Shift key to select more than one column), and then drag the column or columns to the new location.
By default, the check box in the Show row for each field in a query is selected. This means that the values in each field are displayed in the query’s results. By clearing this check box for a field, you remove that field’s values from the query’s result without removing the field from the query.
The capability to hide a field is helpful when you want to use a field to define selection criteria or to sort a query but don’t want to show the field in the query’s results. Fields you use this way are essential to defining the query, but their values don’t need to be shown in the query’s results. You might add an ID field or a date field to a query for these purposes. For the ID field, you might specify a particular customer’s ID. You could use the date field to sort records to display orders sequentially since the start of your fiscal year. The purpose of these fields is to tailor the query. Any reporting or analysis doesn’t require that the query include the values that these fields provide.
To show and hide query fields
1. In the Navigation pane, right-click the query, and then click Design View. 2. In the query design grid, clear the Show check box for any fields you want to hide. 3. Select the Show check box to display a field in the results.
You use the Sort row in the query design grid to specify how Access sorts the records returned by a query. You can sort by a single field or by more than one field. When you specify a sort order for more than one field, Access sorts records according to the order in which the fields appear left to right in the query design grid.
If you want to sort by multiple fields in a particular sequence but also display one of these fields later in the order of the fields, add a second instance of the field, set the sort order for the field, and then clear the Show check box so that the second instance of the field doesn’t appear in the query’s results.
To specify a sort order, click in the Sort row for a field, and then select the sort order you want to apply. The sorting options are Ascending, Descending, and Not Sorted. Be sure to reposition fields as you want them to appear when you are sorting records by more than one field.
To use the Sort row in a query
1. In the Navigation pane, right-click the query, and then choose Design View. 2. In the query design grid, click in the Sort row for the field you want to sort by, and then select Ascending, Descending, or Not Sorted. 3. To sort by more than one field, specify the sort order for the additional fields. In the query design grid, arrange the fields from left to right in the order you want Access to use them to sort records.
At times, you might want to print the results of a query or save the results as a PDF fi le for distribution. To enhance the plain display of the results in the datasheet, you can apply text formatting from the Home tab. For example, you can display or hide gridlines, apply a background color to alternate rows, and apply font attributes such as bold or italic.
The text formatting you apply affects all the records in a query. You can’t, for example, apply bold formatting to only one column of values in the query’s datasheet. Even when you have a single column selected and click Bold in the Text Formatting group, the bold formatting is applied throughout the set of records. Perhaps the most useful text formatting tools are the options for adding or modifying alternate row colors and displaying gridlines to help distinguish the rows and columns of data.
In a query, you can use a field’s Format property to display the values in that field differently from the way the field’s format is specified in the table in which the field is defined. For example, a date field can be defined with the Short Date format in its table but displayed in the Long Date format in a query. You can also use a field’s Caption property in a query to use a different label in the column heading. Setting the Format or Caption property for a field in a query does not define or change the property for the field in its table.
To set formatting properties for a field in a query 1. Open the query in Design view. 2. On the Query Tools Design tool tab, in the Show/Hide group, click Property Sheet. 3. Click in the column for the fi eld you want to format. 4. In the property sheet, enter or select a value for properties such as Format and Caption.