Core Training for Microsoft Office Access 2003

Locating Specific Information

Sorting Information

1To open the Customers table in Datasheet view, on the Objects bar, click Tables, and then double-click Customers.
2To select a criterion for sorting the table records, in the Customers : Table window, click in the Region column.
3To sort the table records by the region name in ascending order, on the Table Datasheet toolbar, click the Sort Ascending button, as indicated.
4To sort the table records by the region name in descending order, on the Table Datasheet toolbar, click the Sort Descending button, as indicated.
5To select and move the Region field to the left of the City field, click the Region column header, and then drag the column to the left of the City field.
6To select the Region and the City columns together, hold down the Shift key, and then click the City column header.
7To sort the records by the Region and City fields in ascending order within each region, on the Table Datasheet toolbar, click the Sort Ascending button.
8To close the Customers table without saving, click its Close button, and in the Microsoft Office Access message box, click No.
9To view an existing form, in the Database window, on the Objects bar, click Forms, and then double-click Customers.
10To sort the records by the Region column in an ascending order, click in the Region box, and then on the Form View toolbar, click the Sort Ascending button.
11To view the sorted records, on the navigation bar of the Customers form window, click the Next Record button, as indicated.
12To close the form, click its Close button.

Filtering Information in Tables and Forms

1To open the Customers table in Datasheet view, on the Objects bar, click Tables, and then double-click Customers.
2To filter the records for Bellevue, click the first instance of Bellevue in the City column, and then, on the Table Datasheet toolbar, click the Filter By Selection button, as indicated.
3Notice that the records of only those customers residing in Bellevue are now displayed. To remove the filter, on the Table Datasheet toolbar, click the Remove Filter button, as indicated.
4To filter the records by using wildcards, click the Records menu, point to Filter, and then click Advanced Filter/Sort.
5To define the criteria, in the CustomersFilter1 : Filter window, double-click the text in the Criteria box, for the purposes of this exercise, press SPACEBAR, and the text will be typed for you.
6To apply the filter, click the Filter menu, and then click Apply Filter/Sort.
7To apply another criteria on the filtered result, click in the first instance of Seattle in the City column, and then, on the toolbar, click the Filter By Selection button, as indicated.
8Notice that the records of customers residing in Seattle are displayed. To remove the filter, on the Table Datasheet toolbar, click the Remove Filter button.
9To close the Customers table, click its Close button, and to save the changes made to the table, in the Microsoft Office Access message box, click Yes.
10To open an existing form, in the Database window, under Objects, click Forms, and then double-click Customers.
11To select another filtering technique, on the Records menu, point to Filter, and then click Filter By Form.
12To select the criteria, in the Customers: Filter by Form window, click the City list arrow, and then click Bellevue.
13To apply the filter, on the Filter menu, click Apply Filter/Sort.
14The records of customers residing in Bellevue city are displayed. To switch back to the filter window, on the Records menu, point to Filter, and then click Filter By Form.
15To add additional criteria, click the Or tab, as indicated, click the City list arrow, and then click Auburn.
16To apply the filter, on the Filter menu, click Apply Filter/Sort, and to switch to the Datasheet view, click the View menu, and then click Datasheet View.
17To remove the filter, on the Form View toolbar, click the Remove Filter button, as indicated, and then to close the Customers form, click its Close button.

Using Complex Expressions

1To open the Customers table in Datasheet view, on the Objects bar, click Tables, and then double-click Customers.
2To filter the records, click the Records menu, point to Filter, and then click Advanced Filter/Sort.
3To clear the design grid, click the Edit menu, and then click Clear Grid.
4To copy a table field to the first column of the Field row of the design grid, in the Customers field list, double-click LastName.
5To specify a criterion, in the LastName column, click the Criteria cell, for the purposes of this exercise, press SPACEBAR, and the text will be typed for you.
6To copy another column to the Field row, in the Customers field list, click below the scroll box on the scroll bar until Region displays, and then double-click Region.
7To specify a criterion, in the Region column, click the Criteria cell, press SPACEBAR, and the text will be typed for you.
8To apply the filter, click the Filter menu, and then click Apply Filter/Sort.
9To switch back to the filter window, on the Records menu, point to Filter, and then click Advanced Filter/Sort.
10To define another filtering criteria, in the LastName column, click the or cell, press SPACEBAR, and the text will be typed for you.
11To apply the filter, on the Filter menu, click Apply Filter/Sort.
12To switch back to the filter window, on the Records menu, point to Filter, and then click Advanced Filter/Sort.
13To define multiple filtering criteria, in the Region column, click the or cell, press SPACEBAR, and the text will be typed for you.
14To apply the filter, on the Filter menu, click Apply Filter/Sort.
15To remove the filter, on the Table Datasheet toolbar, click the Remove Filter button, as indicated.
16To close the Customers table without saving, click the Close button, and then in the Microsoft Office Access message box, click No.

Creating Queries in Design View

1To create a query, in the Database window, on the Objects bar, click Queries, and then double-click Create query in Design view.
2To select a table, in the Show Table dialog box, click Order Details, and then click Add.
3To select another table, in the Show Table dialog box, click Products, click Add, and then click Close.
4To copy a table field to the first column of the Field row of the design grid, in the Order Details field list, double-click OrderID.
5To run the query, click the Query menu, and then click Run.
6To view the query in Design view, click the View menu, and then click Design View.
7To sort the records in ascending order, in the OrderID column, click the Sort field, click the Sort list arrow, and then click Ascending.
8To define a new expression, click in the sixth column of the Field row of the design grid, and then, on the Query Design toolbar, click the Build button, as indicated.
9To view the built-in functions, in the left box in the elements area of the Expression Builder dialog box, double-click Functions, and then click Built-In Functions.
10To select a conversion function, in the second list, click Conversion, and then in the third list, double-click CCur.
11To select a field for the expression, in the Expression Builder box, click «expr», in the first list, double-click Tables, and then click Order Details.
12To continue the selection, in the second list, double-click UnitPrice.
13To insert the multiplication sign in the expression, in the row of operator buttons, click the * button, as indicated.
14To select another field for the expression, in the Expression Builder box, click «Expr», and then in the second list, double-click Quantity.
15To continue defining the expression, for the purposes of this exercise, press SPACEBAR, wait as the text is typed for you, and then in the second list, double-click Discount.
16To complete the expression, press SPACEBAR, wait as the text is typed for you, and then click OK.
17To run the query, on the Query menu, click Run, and then to switch to the Design view, on the View menu, click Design View.
18To specify a new expression name, in the sixth column of the Field row, double-click Expr1, press SPACEBAR, and the text will be typed for you.
19To run the query, on the Query menu, click Run, and then to close the query, click its Close button.
20To save the query with a name, in the Microsoft Office Access message box, click Yes, press SPACEBAR, wait as the text is typed for you in the Query Name box, and then click OK.

Creating and Exploring Queries

1To create a query by using a wizard, double-click Create query by using wizard.
2To specify a table for creating the query, in the Simple Query Wizard window, click the Tables/Queries list arrow, and then click Table: Orders.
3To include all the fields in the query, click the >> button, and to specify another table, click the Tables/Queries arrow, and then click Table: Customers.
4To include fields from the Customers table, under Available Fields, double-click Address, double-click City, and then double-click Region.
5To continue, under Available Fields, double-click PostalCode, and then double-click Country.
6To accept the chosen fields and go to the third page of the wizard, click Next, and then click Next again.
7To specify the query title, for the purposes of this exercise, press SPACEBAR, wait as the text is typed for you in the box, and then click Finish.
8To switch to the Design view, click the View menu, and then click Design View.
9To remove fields from the datasheet, clear the Show check box of the OrderID field.
10To switch to the Datasheet view, click the View menu, and then click Datasheet View.
11To create a parameter query, click the View menu, click Design View, click in the OrderDate column, and then click the Criteria cell.
12To define the criteria, press SPACEBAR, and the text will be typed for you.
13To run the query, click the Query menu, click Run, press SPACEBAR, wait as the parameter value is typed for you in the Enter Parameter Value box, and then click OK.
14To specify the ending date parameter value, press SPACEBAR, wait as the text is typed for you in the Enter Parameter Value box, and then click OK.
15To close the query, click the Close button on the Orders Qry : Select Query window, and in the Microsoft Office Access message box, click Yes.

Performing Calculations in a Query

1To create a new query in Design view, double-click Create query in Design view.
2To select a table, in the Show Table dialog box, double-click Products, and then click Close.
3To copy a field to the grid, in the Products field list, double-click ProductID.
4To copy another column to the grid, in the Products field list, click below the scroll box on the scroll bar until UnitPrice is displayed, and then double-click UnitPrice.
5To use aggregate functions in the query, on the Query Design toolbar, click the Totals button, as indicated.
6To calculate the total number of records, in the ProductID column, click the Total cell, click the Total arrow, and then click Count.
7To calculate the average of all the UnitPrice values, in the UnitPrice column, click the Total cell, click the Total arrow, and then click Avg.
8To run the query, click the Query menu, click Run, and to switch to the Design view, click the View menu, and then click Design View.
9To define a new expression, click the third cell in the Field row, for the purposes of this exercise, press SPACEBAR, and the text will be typed for you.
10To calculate the total value, in the third column, click the Total cell, click the Total arrow, and then click Sum.
11To specify a new expression name, in the third column of the Field row, double-click Expr1, press SPACEBAR, and the text will be typed for you.
12To run the query, click the Query menu, click Run, and to close the query without saving, click its Close button, and then in the Microsoft Office Access message box, click No.