Hands-On Exercises

Download as a Word document: Hands-On Exercise Ch07 (33.0K)

In this exercise you will establish relationships, specify an outer join, and create special queries. To proceed:

1. Open the data file AC07HO01.
2. Open the relationship window and add tables:
   CLICK: Relationship button on toolbar
   DOUBLE CLICK: 1 tblCustomers
   DOUBLE CLICK: 1 tblCustExcursion
   DOUBLE CLICK: 1 tblExcursion
3. Define the relationship between 1 tblCustomers and 1 tblCustExcursion as an outer join and enforce referential integrity,
   CLICK: CustomerID in 1 tblCustomers
   DRAG: to cover CustomerID in 1 tblCustExcursion
   CLICK: Check box to Enforce Referential Integrity
   CLICK: Check box to Cascade Updates
   CLICK: Join Type command button
   CLICK: Option Button 2: Include all records from 1 tblCustomers and
   CLICK: OK command button
   CLICK: Create command button
4. On your own create the relationship between 1 tblCustExcursion and 1 tblExcursion, on the ExcursionID field enforce referential integrity, and cascade updates only. Then close the relationship window, and save changes.
5. To make the outer join work in a query, you will need to create two queries, one with the two tables in the outer join relationship, and another with the first query and the third table. To create these queries:
   DOUBLE CLICK: Create query in design view
   SELECT: 1 tblCustomers,
   PRESS: Shift key, hold down while:
   SELECT: 1 tblCustExcursion
   CLICK: Add command button
   CLICK: Close command button
6. Add the following field to the QBE Grid:
   All fields from the 1 tblCustomers table,
   From the 1 tblCustExcursion: ExcursionID and NumberPeople
7. Save the query as: "qryCustomersExc"
8. Now create the second query:
   DOUBLE CLICK: Create query in design view
   SELECT: 1 tblExcursion
   CLICK: Add command button
   CLICK: Queries Tab
   SELECT qryCustomersExc
   CLICK: Add command button
9. Modify the relationship between qryCustomersExc and 1tblExcursion
   DOUBLE CLICK: join line between field lists
   SELECT: Option button 3: "Include all records from qryCustomersExc and"
   CLICK: OK command button
   Notice the change in the join line. At which table is the arrow pointing?
10. Run the query.
    CLICK: Run button on toolbar
11. Adjust column widths to best fit. Save the query as "qryNewExcResults"
12. Review the results of the query. If you have a printer attached, print the datasheet. Then, close the query datasheet window.
13. Lets find records for customers who have not signed on for an excursion using the Find Unmatched query wizard,
    CLICK: New button on Query Window toolbar
    SELECT: Find Unmatched Query Wizard
    CLICK: OK command button
14. Select the options in the query wizard. In the first dialog box:
    SELECT: Table: 1 tblCustomers
    CLICK: Next
    SELECT: Table: 1 tblCustExcursion
    CLICK: Next 2 time
    SELECT: First Name, Last Name, Address, City, State, ZipCode and PhoneNumber fields
    CLICK: Next
    TYPE: qryCustomersWithoutExcursions
    CLICK: Finish
    Four records are selected. Do these match with the blank fields from step 12 above?
15. Close the query datasheet window, close the database. Then, exit Access.

Find-A-Buddy Dive Club

In this exercise you will practice using select queries, parameter queries, and special queries.

1. Open the data file AC07HO01.
2. Ensure that the database window is displaying the query objects by clicking the query tab.
3. The first thing to do is to create a select query to develop a roster for the newly implement dive trip packages.
   DOUBLE CLICK: Create new query in design view
   SELECT: 2 tblDivers, 2 tblDiverTrips, and 2 tblTrips tables
   CLICK: Add command button
4. Now add fields to the QBE Grid.
   DOUBLE CLICK: FirstName, LastName, and Phone from the 2 tblDivers
   DOUBLE CLICK: Paid from the 2 tblDiverTrips table
   DOUBLE CLICK: TripDesc, From and To fields from the 2 tblTrips table
5. Sort the query by: TripDesc, then Paid.
   SELECT: TripDesc column in the QBE Grid
   DRAG: TripDesc so it appears to the left of the Paid column
   CHOOSE: Ascending for the sort order of the TripDesc column
   CHOOSE: Descending for the sort order of the Paid column
6. View the results of the new query.
   CLICK: Run
7. Save the query
   CLICK: Save button on toolbar
   TYPE: qryRoster
8. On your own, close the query datasheet window.
9. Two members of the club called to say they cannot go on the trip they registered for, therefore you can delete their record from the trip table. You will use a select query, then change it to a delete query once you have verified the results. Finally you will run the delete query.
   DOUBLE CLICK: Create query in design view
   SELECT: 2 tblDiverTrips table
   CLICK: Add command button
   SELECT: DiverID and Paid fields
   DRAG: Selected fields to QBE Grid
   SELECT: Criteria row for paid
   TYPE: No
   CLICK: Run button
   Two records should be selected
10. Now, return to the query design screen, and change the query to a delete action query.
    CLICK: Design view button
    CHOOSE: Query menu
    SELECT: Delete
    CLICK: Run
    CLICK: Yes command button when you get the delete warning dialog box. You will be returned to the query design window.
    CLICK: Save Button
    TYPE: qryDeleteTripReg
11. Close the query design window.
12. Next you will create a parameter query for members who are interested in trips to look for a trip by starting date.
    DOUBLE CLICK: Create query in design view
    
13. On your own, add the 2 tblTrips and the 2 tblInterests tables to the query design window. Also add the InterestDescription, TripDesc, Destination, From and To fields to the query grid.
14. Now enter the parameter criteria to the QBE Grid.
    CLICK: From criteria row
    TYPE: [Enter the starting date of the trip:]
15. Test the query's operation:
    CLICK: Run
    TYPE: 6/29/01
    CLICK: OK command button
    One record should be selected the trip to Tobermory, ONT.
16. Save the query as "qryTripParameter" close the query window, close the database window and exit Access.

Hands-On Exercises Data Files Ch07 (66.0K)