Core Training for Microsoft Access 2002

Locating Specific Information

Sorting Information in a Table

1 To start sorting information in the Customers table and open the table in Datasheet view, in the Tables pane, double-click Customers.
2 To sort by Region in ascending order, click the first cell in the Region column, click the Records menu, point to Sort, and then click the Sort Ascending button.
3 To reverse the sort order in the Region column, on the Standard toolbar, click the Sort Descending button.
4 To select the Region field and start sorting more than one column, click the Region column header.
5 To move the Region field to the left of the City field, position the mouse pointer over the Region column header, press and hold down the mouse button, drag the Region column to the left until a dark line appears between Address and City, and then release the mouse button.
6 The Region column is already selected. To extend the selection, so that both the Region and City columns are selected, press and hold down the SHIFT key, click the City column header, and then release the SHIFT key.
7 To arrange the records with the regions in ascending order, and the city names also in ascending order within each region, on the Standard toolbar, click the Sort Ascending button.
8 To start moving the Region column back to where it was, click the Region column header.
9 To move the Region field to the right of the City field, position the mouse pointer over the Region column header, press and hold down the mouse button, drag the Region column to the right until a dark line appears between City and Postal Code, and then release the mouse button.
10 To save the changes made in the Customers table, on the Standard toolbar, click the Save button.
11 To close the Customers table, in the upper-right corner of the window, click the lower of the two Close buttons.

Filtering Information in a Table

1 To open the Customers table in Datasheet view, in the Tables pane, double-click Customers.
2 To identify the record on which to filter the data in the Customers table, in the City field, in the first row, click Sidney.
3 To filter the data so that only records of customers from Sidney are displayed click the Records menu, point to Filter, and then click Filter By Selection.
4 To redisplay the entire list of customers and remove the filter, on the Standard toolbar, click the Remove Filter button.
5 To start filtering Customers table records again, in the fifth row of the Postal Code field, position your mouse to the left of V, press and hold down the mouse button, drag it to the right of V3F, and then release the mouse button.
6 To display the records of all customers living in areas that have a postal code starting with V3F, on the Standard toolbar, click the Filter By Selection button.
7 To remove the filter and redisplay the entire list of customers, click the Records menu, and then click Remove Filter/Sort.
8 To start filtering the list of customers to find out how many live outside the United States, in the Country field, click USA in the twelfth row.
9 To apply the filter, click the Records menu, point to Filter, and then click Filter Excluding Selection.
10 To remove the filter, on the Standard toolbar, click the Remove Filter button.
11 To save the Customers table, on the Standard toolbar, click the Save button.
12 To close the Customers table, in the upper-right corner of the window, click the lower of the two Close buttons.
13 To experiment with one more filtering technique, and open the Orders table in Datasheet view, double-click Orders in the Tables pane.
14 To filter orders taken by Michael Emanuel, in the EmployeeID field, on the seventh row, click Emanuel, Michael, and then on the Standard toolbar, click the Filter By Selection button.
15 To start refining the results of this filter, and find all orders taken by Michael Emanuel on January 23, in the OrderDate field, on the seventh row, click 1/23/01.
16 To apply the filter, click the Records menu, point to Filter, and then click Filter By Selection.
17 To remove the filters, click the Records menu, and then click Remove Filter/Sort.
18 To save the Orders table, on the Standard toolbar, click the Save button.
19 To close the Orders table, in the upper-right corner of the window, click the lower of the two Close buttons.

Filtering Information By Form

1 To open the Customers form in Form view, on the Objects bar, click Forms, and then in the Forms pane, double-click Customers.
2 To start filtering on the Customers form, click the Records menu, point to Filter, and then click Filter By Form.
3 To display all last names starting with S, in the Customers : Filter By Form form, click the second Name box, type s*, and then press the ENTER key.
4 Access converts your entry to the desired format. To define another condition for the filter, click the Region box, click the arrow next to the Region box, and then click CA.
5 To see only the customers living in California, whose last names begin with S, click the Filter menu, and then click Apply Filter/Sort.
6 The navigation bar at the bottom indicates that three filtered records are available. To switch back to the filter, on the Standard toolbar, click the Filter By Form button.
7 To add another set of conditions to the filter, click the Or tab at the bottom of the form.
8 To display all last names starting with S, in the second Name box, type s*.
9 To define another condition for the filter, click the Region box, click the arrow next to the Region box, and then click WA.
10 To see the list of customers living in California and Washington, whose last names begin with S, on the Standard toolbar, click the Apply Filter button.
11 To view the second of the six matched records in the filtered Customers form, on the navigation bar, click the Next Record button (right arrow).
12 To close the Customers form, in the upper-right corner of the window, click the lower of the two Close buttons.

Locating Information Using Multiple Criteria

1 To open the Customers table in Datasheet view, on the Objects bar, click Tables, and then in the Tables pane, double-click Customers.
2 To start filtering on the Customers table, using multiple criteria, click the Records menu, point to Filter, and then click Advanced Filter/Sort.
3 To clear the Design grid in the CustomersFilter1 : Filter window, click the Edit menu, and then click Clear Grid.
4 To define a field on which to filter information, in the Customers field list window above the Design grid, double-click LastName.
5 To specify a criteria for filtering the LastName field, click in the Criteria cell under LastName, type s*, and then press the ENTER key.
6 Access converts the criteria to the desired format. To define another field on which to filter information, on the Customers field list window, click the down scroll arrow two times, and then double-click Region.
7 To specify a criteria for filtering the Region field, under the Region field, click the Criteria cell, type ca or wa, and then press the ENTER key.
8 To see the list of customers from California and Washington, whose last names begin with S, click the Filter menu, and then click Apply Filter/Sort.
9 To return to the filter window, click the Records menu, point to Filter, and then click Advanced Filter/Sort.
10 To specify another criteria for filtering the LastName field, in the LastName column, click in the Or cell, type b*, and then press the ENTER key.
11 To see the results of this filter, click the Filter menu, and then click Apply Filter/Sort.
12 To return to the filter window and remove the names of people starting with B, and living outside California and Washington, on the Windows taskbar, click the CustomersFilter1 : Filter button.
13 To refine the filter criteria, in the intersection of the Or row and the Region column, type ca or wa, and then press the ENTER key.
14 To apply the filter again to see only customers from California and Washington, click the Filter menu, and then click Apply Filter/Sort.
15 To close the Customers table, in the upper-right corner of the window, click the lower of the two Close buttons.
16 To close the table without saving the changes, in the warning box that appears, click NO.

Creating a Query with a Wizard

1 To start creating a query with a wizard, on the Objects bar, click Queries, and then in the Queries pane, double-click Create query by using wizard.
2 To specify a table on which to base the query, in the Simple Query Wizard dialog box, click the arrow next to the Tables/Queries box, and then click Table: Orders.
3 To move the list of fields from the Available Fields pane to the Selected Fields pane, click the >> button.
4 To specify another table on which to base the query, in the Simple Query Wizard dialog box, click the arrow next to the Tables/Queries box, and then click Table: Customers.
5 To select a field in the Customers table, in the Available Fields pane, double-click Address.
6 To accept the selections and proceed to the next step, click Next.
7 To accept the default option of showing details in the results of the query, click Next.
8 To modify the default title of the query and finish creating it, type Orders Qry, and then click Finish.
9 Access runs the query and displays the results in Datasheet view. To view the query in Design view, on the Standard toolbar, click the View button.
10 To remove the OrderID field from the results datasheet, in the OrderID column, click the Show check box.
11 To confirm that the OrderID, CustomerID, and EmployeeID fields are not displayed in Datasheet view, on the Standard toolbar, click the View button.
12 To switch back to the query’s Design view, on the Standard toolbar, click the View button.
13 To convert this query to a parameter query, position the mouse pointer on the intersection of the OrderDate column and the Criteria row, and click the cell.
14 To display a dialog box requesting the date range each time you run the query, type Between.
15 To run the query, on the Standard toolbar, click the Run button (the exclamation point).
16 To specify the first parameter of the range, in the Enter Parameter Value dialog box, in the Type the beginning date: box, type 1/1/01, and then click OK.
17 To specify the second parameter of the range, in the Enter Parameter Value dialog box, in the Type the ending date: box, type 1/31/01, and then click OK.
18 Only the orders between the parameter dates are displayed in the datasheet. To save the query, on the Standard toolbar, click the Save button.
19 To close the query datasheet, in the upper-right corner of the window, click the lower of the two Close buttons.

Creating a Query in Design View

1 To start creating a query in Design view, in the Queries pane, double-click Create query in Design view.
2 To specify which tables and saved queries to include in the current query, on the Tables tab of the Show Table dialog box, double-click Order Details, and then double-click Products.
3 To close the Show Table dialog box, click Close.
4 To include a field in the query, in the Order Details field list window, position the mouse pointer over OrderID, press and hold down the mouse button, drag the pointer down to the first cell in the Field row in the Design grid, and then release the mouse button.
5 To run the query and display the results of the query in Datasheet view, on the Standard toolbar, click the Run button (exclamation point).
6 To return to Design view, on the Standard toolbar, click the View button.
7 To sort the results on the OrderID field, click the intersecting cell of the Sort row and the OrderID column, click the arrow in the cell, and then click Ascending.
8 To add a field for calculating the extended price, in the Field row, click the sixth column from left, and then on the Standard toolbar, click the Build button.
9 To start building an expression, in the Expression Builder dialog box, in the first column of the elements area, double-click the Functions folder, and then click the Built-In Functions subfolder.
10 To limit the functions in the third column to the Conversion category, in the second column of the elements area, click Conversion.
11 To insert the currency conversion function in the Expression box, in the bottom half of the Expression Builder dialog box, in the third column of the elements area, double-click Ccur.
12 To select <<expr>>, in the Expression box, click <<expr>>.
13 To replace <<expr>> with another element, in the first column of the elements area, double-click the Tables folder, click the Order Details subfolder, and then in the second column, double-click UnitPrice.
14 To start specifying a formula for multiplying the amount in the UnitPrice field by the amount in the Quantity field, in the row of operator buttons below the Expression box, click the * (asterisk) button.
15 To replace <<Expr>> with another element in the expression, click <<Expr>>, and then in the second column of the elements area, double-click Quantity.
16 To compute the percentage the customer will pay after discount, in the Expression box, type *(1-.
17 To complete the formula, in the second column of the Expression Builder dialog box, double-click Discount, and in the Expression box, type ).
18 To move the mouse pointer to the beginning of the expression in the Expression box, press the HOME key.
19 To view the complete expression, position your mouse pointer over the right edge of the Expression Builder dialog box, and when a double-headed arrow appears, press and hold down the mouse button, drag the mouse pointer to the right, until the complete expression is displayed, and then release the mouse button.
20 To close the Expression Builder dialog box, and copy the expression to the Design grid, click OK.
21 To complete the entry of the expression in the Design grid, press the ENTER key.
22 To start modifying the default name of the expression, double-click Expr1.
23 To assign a name to the expression, type ExtendedPrice.
24 To see the result of the query in Datasheet view, on the Standard toolbar, click the View button.
25 To close the query datasheet, in the upper-right corner of the window, click the lower of the two Close buttons.
26 To save the query, in the warning box that appears, click Yes.
27 To assign a name to the query, in the Save As dialog box that appears, type Order Details Extended, and then click OK.

Performing Calculations in a Query

1 To start performing calculations in a query, in the Queries pane, double-click Create query in Design view.
2 To specify a table for the current query and close the dialog box, on the Tables tab of the Show Table dialog box, double-click Products, and then click Close.
3 To specify fields in the query, in the Products field list window, double-click ProductID, click once below the scroll box on the scroll bar, and then double-click UnitPrice.
4 To add a row named Total to the Design grid, on the Standard toolbar, click the Totals button.
5 To add a function that will count the number of records containing a value in the ProductID field, position the mouse pointer in the intersection of the Total row and the ProductID column, click the cell, click the arrow in the cell, and then click Count.
6 To add another function that will return the average of all the UnitPrice values, press the TAB key, click the arrow in the cell at the intersection of the Total row, and the UnitPrice column, and then click Avg.
7 To run the query and display the results of the query in Datasheet view, on the Standard toolbar, click the Run button (exclamation point).
8 To return to Design view and begin modifying the query, on the Standard toolbar, click the View button.
9 To build an expression to multiply the price of each product by the number of units in stock, in the Field row, click the third column, type =UnitPrice*UnitsInStock, and then press the ENTER key.
10 To find the sum of all the values calculated by the expression, position the mouse pointer at the intersection of the Total row and the Expr1: [UnitPrice]*[UnitsInStock] column, click the cell, click the arrow in the cell, and then click Sum.
11 To rename the expression, double-click Expr1, and then type Value of Inventory.
12 To run the query again and display the results of the query in Datasheet view, on the Standard toolbar, click the Run button (exclamation point).
13 To close the query without saving it, in the upper-right corner of the window, click the lower of the two Close buttons, and then in the warning box, click No.