BIS-1120 Access 3 Review Training

In Query Design view of the Store List query, use the Expression Builder to create a calculated field that divides the RevenueGoal field by 52 as the last field in the query, then run the query.

*In Query Design view, right-click the first blank Field cell. *Click Build on the shortcut menu *Enter [RevenueGoal]/52 in the Enter an Expression to define the calculated query field box. *Click OK in the Expression Builder dialog box to enter the new expression into the new field cell. *In the Results group, click the Run button to view the results of the calculated field.

Create a new select query in Query Design View using the Stores table. Include the following fields in the following order: MetroArea State StoreID Run the query and save it with the name MetroListing.

Click the Create tab on the Ribbon In the Queries group, click the Query Design button. Click Stores Click Add Click Close in the Show Table dialog box Drag the MetroArea field from the Stores field list to the first column of the query grid. Drag the State field from the Stores field list to the second column of the query grid. Drag the StoreID field from the Stores field list to the third column of the query grid. In the Results group on the Query Tools Design tab, click the Run button. Click the Save button Type MetroListing in the Query Name box of the Save As dialog box. Click OK

Create a new select query using Query Design View using the Managers and Stores table. Include the LastName field from the Managers table. Include the StoreID and Phone fields from the Stores table, and in that order. Run the query and save it with the name ManagerPhones.

Click the Create tab on the Ribbon. In the Queries group, click the Query Design button. Click Managers. Click Add. Click Stores. Click Add. Click Close in the Show Table dialog box. Drag the LastName field from the Manager field list to the first column of the query grid. Drag the StoreID field from the Stores field list to the second column of the query grid. Drag the Phone field from the Stores field list to the third column of the query grid. Click the Run button in Query Tools Design tab. Click the Save button. Type ManagerPhone in the Query Name box of the Save As dialog box. Click OK.

In Query Design view of the ManagerList query, add the text Manager Code as a Caption for the ManagerID field.

Click the ManagerID field in the query grid. In the Show/Hide group on the Query Tools Design tab, click the Property Sheet button. Click the Caption box Type Manager Code and Press ENTER

In Query Design view of the StoreRevenue query, set the Format property fot the calculated Month Goal field to Currency

Click the MonthGoal calculated field in the query grid to select it On the ribbon, on the query tool design tab, in the show/hide group, click the property sheet button On the General tab of the property sheet, click the Format Property Click the format arrow Click currency

In Query Design view of the ManagerList query, hide the ManagerID field.

Click the Show check box of the ManagerID field to clear the check

In Datasheet view of the Managers table, display the subdatasheet for Rich Fitzpatrick's record, which has a Manager ID field value of M-03.

Click the expand button to the left of the third record with the ManagerID value of M-03 to display its subdatasheet, the related records from the Stores table.

In Query Design view of the ManagerList query, move the ManagerID field to the fourth column of the query grid.

Click the field selector for the ManagerID field to select it. Drag the field selector to the new location as indicated by the black indicator line. Release the mouse button to complete the move.

Add a criterion to the ManagerList query to select only those records where the DateOfHire field value is equal to 1/1/2015. Then, run the query to display it in DataSheet view.

Click the first Criteria cell for the DateOFHire field. Type 1/1/2015 in the cell In the results group, click the Run button

Add OR criteria to the ManagerList query to select only those records where the ManagerID field value is M-10 OR the State field value is TX, view the query in Datasheet view.

Click the first Criteria cell for the ManagerID field Type M-10 Click the second Criteria cell for the State field Type TX Click the Run button

Add AND criteria to the ManagerList query to select only those records where the ManagerID field value is M-10 AND the State field value is MO. Then, view the query in Datasheet view.

Click the first Criteria cell for the ManagerID field, then type M-10 in the cell Click the first Criteria cell for the State field. Type MO in the cell. Click the Run button

Add a criterion to the ManagerList query to select only those records where the State field value is TX. Then, run the query to display it in Datasheet view.

Click the first Criteria cell for the State field Type TX in the cell Click the Run button

Add a criterion to the StoreByState query to select only those records where the RevenueGoal field value is greater than $700,000. Then run the query to display it in Datesheet view

Click the first criteria cell for the RevenueGoal field Comparison operations include > (greater than), < (less than), >= (greater than or equal to), and <= (less than or equal to) Type >700000 in the cell In the results group, click the Run button

Delete the District of Columbia, DC record from the State table

Click the record selector for the District of Columbia, DC record In the Records group on the Home tab, click the Delete button Click Yes to confirm the deletion of the record

In Datasheet view of the StoresByState query, sort the records in ascending (A to Z) order based on the values in the StateName field.

In Query Datasheet view, click the arrow to the right of the StateName field Click the Sort A to Z option on the menu

In Datasheet view of the Managers table, hide the DateOfHire field.

In Table Datasheet view of the Managers table, right-click the DateOfHire field name. Click Hide Fields on the shortcut menu

In Report Design view of the ManagerEmailList report, resize the Report Header section to be 1.5" tall.

In report design view of the ManagerEmailList report, drag the top edge of the Page Header section up to the 1.5" mark on the vertical ruler

Add the ManagerEmailList report to the custom Email group

In the Navigation Pane title bar, click the arrow Click custom on the drop down list Righ-click the ManagerEmailList report Point to add to group Click e-mail

In Query Design view of the RevenueByState query, add the Total row to the query grid.

In the Query Design view on the Query Toolds Design tab on the Ribbon, in the Show/Hide group click the Totals button.

In Query Design view of the SotresByState query, add an ascending sort order to the StateName and City fields, and display the query in Datasheet view.

In the Sort row of the query grid, click the Sort cell for the StateName field. Click the Sort cell arrow for the StateName field In the list of possible sort orders, click Ascending Click the Sort cell for the City field Click the sort cell arrow for the City field. In the list of possible sort order, click Ascending In the results group, click the Run button

In Datasheet view of the Managers table, set the font size of the datasheet to 12

On the Home tab of the Ribbon, in the Text Formatting group, click the Font Size arrow Click 12 on the drop-down list

In Datasheet view of the StoreList query, add a Total row

On the Home tab on the Ribbon, in the Records group, click the totals button

In Query Design view of the StoresByState query, group the records by StateName, count the ManagerID field values, and run the query.

On the Query Tools Design tab on the Ribbon, in the Show/Hide group, click the Totals button Click group by in the total row for the ManagerID field Click the Group by arrow click Count On the Query tools design tab, in the reseults group, click the run button

Rename the Stores report to be StoresByState

Rick-click the Stores report in the Navigation Pane. Click Rename on the short-cut menu Type StoresByState and press ENTER

Create a new query by copying the ManagerList query to a new query object named ManagerDetails.

Right-click the ManagerList query. Click Copy on the shortcut menu. Right-click a blank area of the Navigation Pane. Click Paste on the shortcut menu. Type ManagerDetails in the Query Name text box. Click OK to complete the process.

In Query Design view add an ascending sort order to the LastName field of the ManagerList query, and display the query in Datasheet view.

To set sort orders in Query Design view, you work in the Sort row of the query grid. To sort by the LastName field value, Click the Sort cell for the LastName field. Click the Sort cell list arrow for the LastName field. In the list of possible sort orders, click Ascending. In the Results group, click the Run button.