Shelly Cashman Access

Chapter 2: SAM Project

Great Outdoors Tours

CREATING AND MODIFYING QUERIES

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 started developing a simple database in Access 2013 to organize its records. The company would like your help modifying some of the queries in the database and would also like you to create some new queries that will help increase the company's efficiency.

GETTING STARTED

O Download the following file from the SAM website:
o SC_Access2013_C2_P1a_FirstLastName_1.accdb
o Open the file you just downloaded and save it with the name:
o SC_Access2013_C2_P1a_FirstLastName_2.accdb
o 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.
o 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. Open the Guide Contact Query in Design view. Delete the YearsExperience the field from the query, then save, run, and close the query.

2. Create a new query in Query Design view based on the Guides table with following options:

a. Add the fields GuideID, FirstName, LastName, and YearsExperience to the query in that order.
b. Add an ascending sort order on the YearsExperience field.
c. Save the query with the name Guide Experience Query.

Run the Guide Experience Query and then close it.

3. Create a new parameter query in Design view based on the Guides table with the following options:

a. Add the FirstName, LastName, PhoneNumber, Address, State, and Postal Code fields (in that order) from the Guides table to the query.
b. Add the criterion [Enter State] (including brackets) to the State field.
c. Save the query with the name State Guide Query

Run the State Guide Query to confirm it works (Hint: If you use ME for the parameter value, the query should return 3 records.) Save and close your query.

4. Create a new query in Design view based on the Trips and Guides tables with the following options:

a. Add the TripName field from the Trips table to the query.
b. Add the FirstName, LastName, and PhoneNumber fields (in that order) from the Guides table to the query.
c. Save the query with the name Guide Assignments Query.

Run the Guide Assignments Query, then save and close it.

5. Create a Crosstab query based on the Trips table with the following options:

a. Use only data from the Trips table in the query.
b. Use the State field for the row headings.
c. Use the Season field for the column headings.
d. Use a Count of the TripID field as the calculated value for each row and column intersection and include row sums in the crosstab query.
e. Save the query with the name State-Season Crosstab.

View the query, then save and close it.

6. Open the 8 Person Trips Query in Design view. Modify the query to show only records where the GroupSize field is equal to 8. Run the 8 Person Trips Query, save and then close it.

7. Open the Beginner NH Trips Query in Design view and add criteria to select only those records where the State field is equal to NH and the Level field is equal to Easy. Run the query, then save and close it.

8. Open the Challenging Trips Query in Design view, hide the Level field in the query, then save and close it.

9. Open the Early Seasons Query in Design view and add criteria to select only those records where the Season field is equal to Spring or to Summer. Run the query, then save and close it.

10. Open the Maine Trips Query in Design view. Modify the query to sort the records in ascending order by the TripName field and the StartLocation field. Run the Maine Trips Query, save, then close it.

11. Open the Short Trips Query in Design view and add criteria to select only those records where the value in the Distance field is less than 7. Run the query, then save and close it.

12. Open the Trips by Type Query in Design view and modify it by adding totals to the query. For the Type field, set the total row to Group By. For the TripName field, set the total row to Count. Run the query, then save and close the query.

13. Open the C Trips Query in Design View and add the C* wildcard criteria to the TripName field, so that the query only returns records where with trip names that begin with the letter C. Run the query, then save and close the query.

14. Use the Form button to create a simple form based on the Challenging Trips Query. Save the form as Challenging Trips Form and close the form.

15. Create a new report (shown in Figure 1) using the Report Wizard based on the All Trips Query with the following options:

a. Include all fields from the All Trips Query in the report.
b. The report will automatically be grouped by the State field, but use no additional grouping in the report.
c. Use no additional sorting in the report.
d. Use a Stepped layout and Portrait orientation for the report.
e. Set the title of the report to All Trips Report

Preview the report, then save and close it.

Figure 1: All Trips Report: See in attached.

Close and save 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.

Attachment:- access_project_steps.rar