Microsoft Access task

Chapter 1: SAM Project 1a

Great Outdoors Tours

CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECT

PROJECT DESCRIPTION

Great Outdoors Tours is a small business that organizes day-long, guided trips of Massachusetts, Maine, New Hampshire, and Vermont. The company provides customers with a wide variety of hiking, biking, and paddling trips for all levels of experience. Outdoors Tours has added a number of new trips and guides over the last few years and the growth has necessitated a change in the way the company manages its operations. Great Outdoors Tours has decided to organize their records in using Access 2013.The company would like your help creating some tables, importing some of their records from a spreadsheet, and creating some basic forms and reports.

GETTING STARTED

Download the following file from the SAM website:

· SC_Access2013_C1_P1a_FirstLastName_1.accdb

Open the file you just downloaded and save it with the name:

· SC_Access2013_C1_P1a_FirstLastName_2.accdb

· Hint: If you do not see the .accdb file extension in the Save file dialog box, do not type it. Access will add the file extension for you automatically.

To complete this Project, you will also need to download and save the following support file from the SAM website:

· support_SC_A13_C1_P1a_Trips.xlsx

Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. Create a new table in Datasheet view with the following options:

a. Rename the default primary key ID field to State and change the Data Type to Short Text. (Tip: State should remain the primary key.)

b. Change the field size of the State field to 2.

c. Add a new field with the name StateName and change the data type to Short Text.

d. Save the table with the name States.

2. With the States table still open in Datasheet view, add the four records shown in Table 1.

Table 1: States table

© 2014 Cengage Learning.

State	StateName
MA	Massachusetts
ME	Maine
NH	New Hampshire
VT	Vermont

3. Resize the StateName field so that all field values are completely visible. Save and close the States table.

4. Open the Guides table in Datasheet view. Add a new field following the PostalCode field with the field name YearsExperience and the Number data type. Close the table.

5. Open the Trips table in Design view and make the following changes to the Distance field.

a. Change the data type from Short Text to Number

b. Change the Caption property to Distance (Miles)

6. With the Trips table still open in Design view, make the following changes to the GroupSize field:

a. Change the data type from Short Text to Number

b. Change the Decimal Places property from Auto to 0.

c. Change the Caption property to Maximum Group Size

7. With the Trips table still open in Design view, change the Field Size property for the State field to 2. Save the changes to the Trips table and then close it. (Note: Because there were changes to data type and field sizes, the “Some data may be lost” warning message appears. The data fits within the valid ranges, so ignore this message and continue saving the table.)

8. Import the data from the Excel file support_SC_A13_C1_P1a_Trips.xlsx available for download from the SAM website. Append the records into the Trips table. Do not create a new table and do not save the import steps.

9. Open the Trips table in Datasheet view, navigate to the third record (which has a TripID field value of CLNHP1) and enter KS01 for GuideID field value. Navigate to the previous record (which has a TripID field value of BPMEH1) and enter RH01 for the GuideID field value. Close the table.

10. Open the Guide Listing Query in Design view and add a criterion to select only those records with a State field value of NH. Run the query, then save, and close the query.

11. Use the Simple Query Wizard to create a query based on the Trips table with the following options:

a. Include all fields from the Trips table in the query.

b. Make sure the Detail option button is selected.

c. Save the query with as Trips Query (which is the default name).

Close the query.

12. Create a simple form for the Trips table. Save the form as Trips Form and close the form. (Hint: Use the Form button to create this form.)

13. Create the simple report shown in Figure 1 for the Trips table. (Note: Your report may show a different date and time than Figure 1.) Do not add any totals and do not resize any columns. Save the report as Trips Report and close the report.

Figure 1: Trips Report

Microsoft product screenshots used with permission from Microsoft Corporation.

Copyright © 2014 Cengage Learning. All Rights Reserved.

14. Open the Guide Update Form in Form view and navigate to the second record, which has the GuideID of BR01. Change the Address field value for this record to 140 Oak Stump Rd. and then close the Guide Update Form.

15. Open the Guide Contact Numbers report in Layout view and change the report title from Guide Contact to Guide Contact Numbers. Save and close the report.

16. Open the Reservations table in Datasheet view. Add a new field following the TripDate field with the field name GroupSize and the Number data type.

Save and close any open database object. Compact and repair your database, and then exit Access. Follow the directions on the SAM website to submit your completed project.

2