New book: MOS 2013 Study Guide for Microsoft Access

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 The Microsoft Press Store
click here

Order from Amazon
click here

Order from Barnes & Noble
click here

Order from Ada’s Technical Books and Café
click here

Excerpt from chapter 3, “Create queries” section 3.2: Modify Queries

3.2 Modify queries

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.

Changing the fields in 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:

• Drag additional fields from the field lists displayed in the top pane to a blank row
  in the query design grid.
• Insert a field between two other fields by dragging it to the column in the query
  design grid where you want to place the field. Access moves the other fields in the
  query to the right.
• Add a field by selecting it from the list in the Field row in the query design grid.
• Click Show Table in the Query Setup group to open the Show Table dialog box, and
  then select another table or query to include in the query’s record source.

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.

Showing and hiding query fields

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.

Specifying the sort order for queries

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.

Formatting fields in a query

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.