Hands-On Exercises

Download as a Word document: Hands-On Exercise Ch06 (90.0K)

In this exercise you will practice creating multi-table queries, sorting and calculating.

1. To get started, open the file named AC06HO01.
2. Your screen should look similar to the screen in Figure 6-1. There are six tables, three which will be used in this exercise, three for the next exercise.

3. <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470925/26861/ch06_hoe_image01.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (52.0K)</a>

   Figure 6-1

4. To switch to query design view, first:
   CLICK: Queries object button
   CLICK: New on the queries window toolbar.
5. In the new query dialog box:
   DOUBLE CLICK: Design View
6. To select the tables for this exercise:
   CLICK: 1 CustExcursion
   PRESS: [Shift] Key, and hold then
   CLICK: 1 Excursion
   All three tables that begin with a "1" should be selected
7. Add the tables to the query design window:
   CLICK: [Add] command button
   CLICK: [Close] command button
8. Add fields to the query by example grid:
   DOUBLE CLICK: First Name on the 1 Customers table
   DOUBLE CLICK: Last Name on the 1 Customers table
   DOUBLE CLICK: ExcursionDesc on the 1 Excursion table
   on your own, select Rate and Hours fields from the 1 Excursion table
9. Run the Query,
   CLICK: [Run] button on the query design toolbar
   The results will be displayed in datasheet view.
10. Save the query,
    CLICK: [Save] button on datasheet view toolbar
    Name the query "Cusomters and Excursions"
11. To gather some additional information, lets add a couple of fields to the query. First switch back to design view.
    CLICK: [Design View] button on the datasheet toolbar.
12. Add the NumberPeople Field:
    CLICK: NumberPeople, and
    DRAG: NumberPeople field to the third column in the design grid.
    (Be sure to release the mouse button when finished.)
13. The owner of the company would like to see how his rates compare on a per person basis.
    SCROLL: QBE Grid to show a blank column
    SELECT: Field text box of the first blank column
    TYPE: Per Person:[Rate]/[NumberPeople]
14. View the datasheet. Notice that the numbers are not formatted. Switch back to design view and format the numbers in the calculated column.
    CLICK: [Design View] button
    CLICK: Per Person field text box
    CLICK: [Properties] Button on the toolbar
    SELECT: Format text box, drop down the format list
    SELECT: Currency format
15. On your own, sort the results by Rate in descending order.
16. To view the results,
    CLICK: [Run Query] button
17. Save the changes to the query, close the datasheet window, close the database and exit Access.

Find-A-Buddy Dive Club

In this exercise you practice calculating fields.

1. Open the file AC06HE01, that contains the three tables used for this exercise.
2. Create a new query in design view:
   DOUBLE CLICK: Create query in design view
   SELECT: Divers, DiverInterest, and Interests Tables,
   CLICK: [Add] command button
   CLICK: [Close] command button
3. Concatenate, or join the First and Last names together to appear in a single field in the format of LastName, FirstName.
   CLICK: The field text box for the first column
   TYPE: Name: [LastName]", "[FirstName]
   DOUBLE CLICK: Phone
   DOUBLE CLICK: InterestDescription
   CHOOSE: Sort Ascending for the Interest Description Column
4. Run the query to view the results. Adjust the width of the name column to accomodate the longest name shown. Also adjust the Interest Description column to fit the field name.
5. Save the query as Diver Interests with phone, Close the datasheet window.
6. Let's create a query to show each diver's interests in a spreadsheet format. You will use the Cross tab query wizard, and the query you just created.
   CLICK: New on the query window
   CLICK: Crosstab Query Wizard
   CLICK: [OK] command button
7. In the first query wizard dialog box,
   CLICK: Queries in the View option box
   SELECT: Diver Interests with Phone
   CLICK: Next
8. In the next dialog box
   SELECT: Name as the Row header
   CLICK: Next
9. SELECT: InterestDescription as the column headings
   CLICK: Next
10. SELECT: Phone as the field to calculate on
    CLICK: Count
    Ensure that the summary box is left unchecked.
    CLICK: Next
11. Name the query Diver Interest Crosstab and, click finish.
12. Adjust the column headings to a best fit
13. Print the results of the query
14. Close the datasheet window, save the changes, close the database, and exit Access.

Hands-On Exercises Data Files Ch06 (49.0K)