A 7-Step Exercise

Several years ago, Connor and Siobhan Dempsey moved to Boulder, Colorado, drawn by their love of the mountains and their interest in outdoor activities of all kinds. This interest led them to form the Mountain River Adventures center. The center began as a whitewater rafting tour provider, but quickly grew to encompass other activities, such as canoeing, hiking, camping, fishing, and rock climbing.

From the beginning, Connor and Siobhan have used computers to help them manage all aspects of their business. They recently installed Access and created a database named Outdoors to store information about clients, equipment, and types of guided tours they provide. They want you to create various queries, forms, custom report and mailing labels for the Outdoors database.

Step 1: Use the Import Spreadsheet Wizard to create a new table named Rafting Trip. The data you need to import is contained in the Rafting Trip workbook.

Step 2: Open the Booking table in design view. Change the Trip# field data type to Lookup Wizard. Lookup value in the Rafting Trip table; select the Trip#, River, and Fee/Person fields from the Rafting Trip table; use the label River; and accept all other Lookup Wizard default choices. View the Booking table datasheet, resize the River column to its best fit, and then save and close the table.

Step3: Define the necessary one-to-many relationships between the database tables, as follows: between the primary Client table and the related Booking table, and between the primary Rafting Trip table and the related Booking table. Select the referential integrity option and both cascade options for each relationship.

Step 4: Use the Rafting Trip table to determine the minimum, average, and maximum Fee/Person for all trips. Set the caption property of the three fields to Lowest fee, Average Fee, and Highest Fee, respectively. Also set the properties so that the results of the three fields are displayed as currency with two decimal places. Save the query as Fee Statistics.

Step 5: Create a new query based on the Rafting Trip and Booking tables. Select all fields from the Rafting Trip table, and select all the fields except River from the Booking table. Add a calculated field named TripFee that multiples the Fee/Person and People fields; use the IIf function to reduce the TripFee by 10% if the People field value is greater than six. Set the calculated field's Format property to Standard. Save the query as Booked Rafting Trips.
Step 6: Use the Form Wizard to create a main form and s subform. Select the Client#, ClientName, City, State/Prov, and Phone fields from the Client table for the main form, and select all fields except Client# from the Booking table for the subform. Use Datasheet layout and the standard style. Specify the title Clients and Bookings for the main form and the title Booking subform for the subform. Resize all columns in the subform to their best fit.

Step 7: Use the Report Wizard to create a report based on the primary Client table and the related Booking table. Select all fields from Client table, and select all fields except Client# from the Booking table. Sort the detail records by the TripDate field in ascending order. Choose the outline 1 layout and the compact style. Specify the title Client Bookings for the report, then save the report.

1. Use the following instructions to create the mailing labels:

a. Use the Client table as the source for the mailing labels.

b. Use Avery C2163 labels, and use the default font and color settings. (Hint: Make sure the English option button is selected in the Unit of Measure section.)

c. For the prototype label, place ClientName on the first line; Address on the second line; City, a space, Stat/Prov, a space, and PostalCode on the third line; and Country on the fourth line.

d. Sort by PostalCode and then by ClientName, and then enter the report name Client Labels.

2. Design a switchboard using the following layout. (Refer to the attached instructions)

Mountain River Adventure

1. Run Fee Statistics Query

2. Run Booked Rafting Trips Query

3. Open Clients and Bookings Form

4. Open Clients Bookings Report

5. Open Clients Mailing Labels Report

6. Exit

3. Close the Outdoors database, and then exit Access.

Instructions to Create a Switchboard

1. Open the database and select Form object

2. From the menu bar select Tools, database Utilities, Switchboard Manager

3. Access will inform you that there is no switchboard, would you like to create one? Click on Yes.

4. Edit the Main Switchboard (default)

5. Click on New and type the text for the first switchboard item

6. Select the command

7. Specify which query/form/report should run

Note: Since there is no command to run a query from the switchboard you need to create a macro and instruct the macro to run a query. Here are the instructions to create a macro:

1. Open the database and select Macros object

2. Click on New

3. Set the action to Open Query

4. Specify Query Name

5. Close the macro window and save it under Macro1

Use Run Macro in the switchboard to open a query.

Attachment:- RaftingTrip.zip