Access Module 3 SAM Training
Add a single field sort to a query in Design view. AC 9241 Observe mode Locating a record is much easier if the records are sorted. Using the Sort row in the query design grid is an efficient way to sort records.In the Query Design grid, click the Sort cell for the LastName field.The Sort cell arrow appears for the LastName field.Click the Sort cell arrow.In the list of possible sort orders, click Ascending.An Ascending (a to z) sort order is now specified for the LastName field. Records will be presented by this query in ascending order based on the values in the LastName field.In the Results group, click the Run button.Datasheet view confirms that the records are sorted in ascending sort order on the values in the LastName field.
a
Copy a database object. AC 9259 Observe mode Copying an object such as a query saves a great deal of time and work by letting you edit the copy of the object rather than having to recreate an entire new query.Right-click the ManagerList query.To copy an object, make sure it is closed.Click Copy on the shortcut menu.Right-click a blank area of the Navigation Pane.Click Paste on the shortcut menu.The Paste As dialog box opens with a suggested name for the new object.Type ManagerDetails in the Query Name text box.ManagerDetails replaces the suggested name for the new query.Click OK.The new query appears in the Queries section of the Navigation Pane.
a
Add criteria using AND to a query in Query Design view. AC 9373 Observe mode Queries let you create custom views of fields and records, but adding AND criteria to your query can tailor your selection even further to precisely suit your needs. This enhances your ability to analyze your data.In the query design grid, click the cell on the Criteria row for the ManagerID field.You will enter the first of the two criteria in this cell.Type M-04 in the cell.Click the cell on the Criteria row for the State field.AND criteria are entered on the same row of the query grid. Because both the ManagerID and State fields have a Short Text data type, Access surrounds query criteria for these fields with quotation marks after you click another cell.Type IL in the cell.This is the second of the two criteria.Click the adjacent cell at the right in the query grid.On the Query Tools Design tab on the ribbon, in the Results group, click the Run button.Running the query selects the fields and records specified in Query Design view and displays them in Query Datasheet view. Three records were selected where both criteria are true: a ManagerID value of M-04 as well as a State field value of IL.
s
Create a multi-table query in Query Design view. AC 9369 Observe mode Creating a query based on multiple tables lets you select and order only the fields you want to see rather than including entire tables in the query. Design view gives you the flexibility of sorting and adding other criteria.Click the Create tab on the ribbon.In the Queries group, click the Query Design button.A new, blank query opens in design view with a list of objects available in the database. In the Show Table box, the Clients table is highlighted by default.Click Add.The first step in creating a query in Query Design View is selecting the table or tables that store the fields of information you want to select for the query.Click Managers.Click Add.In the Show Table dialog box, click Close.If the tables have been related in the Relationships window, the one-to-many relationships between the tables also appear in Query Design view.Drag the ClientName field from the Clients field list to the first column of the query grid.Drag the Phone field from the Clients field list to the second column of the query grid.Drag the LastName field from the Managers field list to the third column of the query grid.You can add fields to the query in any order desired by dragging them to a query grid column.In the Results group on the Ribbon, click the Run button.Running the query shows you the Datasheet view for the fields and records that were selected in Query Design view. You can also run the query by clicking the Datasheet View button.Click the Save button.The Save As dialog box opens with a default query name highlighted.In the Save As dialog box, in the Query Name text box, type ManagerPhones as the new query name.Click OK
s
Create a single-table query in Query Design view. AC 9371 Observe mode Creating a query in design view is an easy way to display records in the view that best suit your needs. For example, you can use a query to view only the city and state for each client.Click the Create tab on the ribbon.In the Queries group, click the Query Design button.The Show Table dialog box opens, prompting you to add tables and/or queries to Query Design view. The Clients table is selected by default.In the Show Table dialog box, click Add.Excel displays the Field List for the Clients table.In the Show Table dialog box, click Close.Drag the ClientName field from the Clients field list to the first column of the query grid.Drag the City field to the second column of the query grid.Drag the State field to the third column of the query grid.On the Query Tools Design tab, in the Results group, click the Run button.Running the query shows you the Datasheet view for the fields and records that were selected in Query Design view. You can run the query by clicking the Datasheet View or Run buttons.Click the Save button.The Save As dialog box opens, with a default query name highlighted in the Query Name text box.In the Save As dialog box, type ClientLocations in the Query Name text box.Click OK.The new query is saved and appears in the Navigation pane.
s
Find a record in a datasheet. AC 10330 Observe mode You will likely want to locate a specific entry in a field in a datasheet frequently. Using the Find tool makes this process very efficient.In the ClientsByState datasheet, in the City field, click the first value.It is important to click the field that contains the data used for the find process before using the Find tool. You could click anywhere in the City column; but, for this task, please click the first value in the field.In the Find group on the Home tab on the ribbon, click the Find button.The Find and Replace dialog box opens. To find data, use the Find tab and the Find What text box to enter the criterion used to find a record.In the Find and Replace dialog box, on the Find tab, type Houston in the Find What text box.Click Find Next.Access finds the next record containing "Houston" in the City field.Click Cancel.The value Access found is highlighted in the datasheet.
s
Open a subdatasheet. AC 9263 Observe mode Using a subdatasheet is an efficient way to compare records in related tables. For example, you can view all client records that are related to the manager you are working with.Click the expand button to the left of the third record with the Manager ID field of M-03 to display its subdatasheet, the related records from the Clients table.The subdatasheet for Manager ID M-03 appears, showing two related records from the Clients table. The Managers and Clients tables are connected by a one-to-many relationship. Subdatasheets are only available for the table on the "one" side of a one-to-many relationship.
s
Resize a column in Query Datasheet view. AC 10332 Observe mode Occasionally, you will need to resize a column to fully display the contents of that column with its heading. Resizing is quick and easy using the column separator or shortcut menu.With the ManagerEmail query open in Datasheet view, point to the right border of the field selector (column separator) for the Email field so that the pointer becomes a two-headed arrow, then double-click the field selector.Access resizes the column to its best fit, which is the minimum width needed to display all of the data and the field name in the column.
s
Resize a field list. AC 10315 Observe mode Resizing a field list in Query Design view can make your work more efficient and accurate by letting you see all of the fields in a table.In Query Design view, drag the bottom border of the Clients field list downward until all fields are visible.Resizing field lists in Query Design view does not change the way a query functions. It does improve readability, allowing you to see all of the fields in a field list. When all of the fields are visible, the scroll bar is removed from the field list.
s
