Shelly Cashman Access 2013
Chapter 2: SAM Project 1a
Great Outdoors Tours
Creating and Modifying Queries
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.
Download the following file from the SAM website:
Open the file you just downloaded and save it with the name:
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
Open the _GradingInfoTabletable 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.
1. Open the Guide Contact Queryin Design view. Deletethe YearsExperience 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 the 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 Queryand then close it.
3. Create a new parameterquery in Design view based on the Guidestable with the following options:
a. Add the FirstName, LastName, PhoneNumber, Address, State, and Postal Code fields (in that order) from the Guidestable to the query.
b. Add the criterion [Enter State] (including brackets) to the Statefield.
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 Guidestables 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 Tripstable with the following options:
a. Use only data from the Tripstable 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 includerow 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 Queryin Design view. Modify the query to show only records where the GroupSizefield 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 a 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 Queryin Design view. Modify the query to sort the records in ascending order by the TripName field and the StartLocationfield. 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 Distancefield 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 TripNamefield, 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 Formbutton 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.