Illustrated Access 2013 Unit B: SAM Project 1a Tours Database USING QUERIES PROJECT DESCRIPTION You work for an adventure tour company that operates recreational hiking, biking, and walking tours in Colorado. The company uses a database to track information about tour guides, tours, tour schedules, clients, and sales. You'll create and modify several queries to answer specific questions about the data. GETTING STARTED Download the following file from the SAM website: o IL_Access2013_UB_P1a_FirstLastName_1.accdb Open the file you just downloaded and save it with the name: o o IL_Access2013_UB_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. 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. Use the Simple Query Wizard to create a query based on the Tours table with the following options: a. Include all fields from the Tours table in the query. b. Select the option to show details in the query. c. Set the title of the query to TourMasterList. Run the query, then save and close it. 2. In Datasheet View, change the SalesListing query font to Times New Roman. 3. In Datasheet View, change the SalesListing query font size to 12 pt. Save and close the SalesListing query. Illustrated Access 2013 | Unit B: SAM Project 1a 4. Create a new query in Query Design View based on the Tours and Schedule tables with the following options. (Hint: Resize the Tours field list so all fields are visible.) a. Add the following fields from the Tours table to the query grid in this order: TourName, Level, TrailheadCity, and Cost. b. Add the following field from the Schedule table to the query grid, after the Cost field: StartDate. c. Save the query with the title TourSchedule, then display it in Datasheet View. 5. Use the Save Object As feature to save the TourSchedule query as a new query with the title TourBeginner. 6. Open the TourBeginner query in Design View, then add criteria to select only those records where the Level field value equals Beginner. Switch to Datasheet View, confirm the proper records appear in the TourBeginner query, then save and close it. 7. Create a new query in Query Design View based on the Clients table with the following options. (Hint: Resize the Clients field list to see all of the fields.) a. Add the following fields from the Clients table to the query grid in this order: LastName, FirstName, City, and State. b. Add an ascending sort order to the LastName and FirstName fields. c. Save the query with the name ClientList, then display it in Datasheet View. 8. Use the Save Object As feature to save the ClientList query as a new query with the title ClientListTXCO. 9. Switch to Design View for the ClientListTXCO query, then enter criteria to select only those records with a State field value of TX (Texas) or CO (Colorado). Switch to Datasheet View, confirm the proper records appear in the ClientListTXCO query, then save and close it. 10. Create a new query in Query Design View based on the Tours table with the following options. (Hint: Resize the Tours field list to see all of the fields.) a. Add the following fields from the Tours table to the query grid in this order: TourName, MinimumAge, TrailheadCity, and Cost. b. Add criteria to select only those tours where the MinimumAge field value is greater than or equal to 10 and the Cost field value is less than or equal to 100. (Hint: Remember that all records appearing in the query will fulfill both criterianot just one or the other.) c. Save the query with the name LowCostTours, display the query in Datasheet View, then close it. Illustrated Access 2013 | Unit B: SAM Project 1a 11. Open the Clients table in Datasheet View, then Filter By Selection to find only those records where the State field equals CA (California). Make the following changes to the record for Greg Huber (ClientID 6) (Hint: Do not include the periods shown at the end of sub-steps a, b, and c.): a. Change the Address field to 400 Oak Street. b. Change the City field to Austin. c. Change the State field to TX. Remove the filter, then save and close the table. 12. Open the SalesListing query in Design View, then enter criteria that use a wildcard character to find all records that have a StartDate field value of any day in the month of June 2015. 13. In the SalesListing query, delete the SaleDate field from the query grid, then delete the Guides table from the query. 14. In the SalesListing query, switch to Datasheet View and hide the Cost field. Save and close the SalesListing query. Save and close any open objects in your database. Compact and repair your database, close it, and exit Access. Follow the directions on the SAM website to submit your completed project. Illustrated Access 2013 Unit F: SAM Project 1a Volunteer Activities IMPROVING QUERIES PROJECT DESCRIPTION As a part-time emergency volunteer, you have developed a database to help track a network of volunteers and the time they donate to various emergency relief activities. In this project you will work to improve queries to analyze data. GETTING STARTED Download the following file from the SAM website: o IL_Access2013_UF_P1a_FirstLastName_1.accdb Open the file you just downloaded and save it with the name: o IL_Access2013_UF_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. 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 query in Design View based on the VolunteerHours and Activities tables with the following options: a. Use the ActivityDescription field from the Activities table and the Hours field from VolunteerHours table (in that order) in your query. b. Add the Totals row to the query (in Design view) and Sum the Hours field. c. Save the query with the name HoursSummary and run the query. (Tip: The Hours field will be renamed SumOfHours when you run this query.) d. With the HoursSummary query in Datasheet View, add the Total row to the datasheet, and Sum the SumOfHours field in the datasheet as shown in Figure 1. Save and close the HoursSummary query. Figure 1: HoursSummary query Illustrated Access 2013 | Unit F: SAM Project 1a 2. Microsoft product screenshots used with permission from Microsoft Right-click Corporation. the ActivityLog query, then copy and paste it with the name Copyright 2014 Cengage Learning. All Rights Reserved. ActivityLogMay23. Open the ActivityLogMay23 query in Design view, add criteria to select only those records with a WorkDate field value of 5/23/13 and an Hours field value of greater than or equal to 8. (Tip: Records returned by this query should meet both criteria.) Switch to Datasheet View, confirm your query returns the records shown in Figure 2, then save and close the ActivityLogMay23 query. Figure 2: ActivityLogMay23 query 3. Right-click the ActivityLog query, then copy and paste it with the name Microsoft product screenshots used with permission from Microsoft ActivityLogMercy. Open the ActivityLogMercy query in Design view, and Corporation. make the following updates: a. Add criteria to select records with Mercy anywhere in the ActivityDescription field or an Hours field value of greater than 8. (Tip: Records returned by this query should meet one or both criteria.) b. Add two sort orders to sort the records in ascending order by LastName field and then in ascending order by the ActivityDescription field Illustrated Access 2013 | Unit F: SAM Project 1a Switch to Datasheet View for the ActivityLogMercy query, confirm the query returns the records shown in Figure 3, then save and close the ActivityLogMercy query. Figure 3: ActivityLogMercy query Microsoft product screenshots used with permission from Microsoft Corporation. 4. Create a new query in Design View based on the Activities, VolunteerHours and Zips tables with the following options: a. Use the ActivityDescription field from the Activities table, the Hours field from VolunteerHours table, and the State field from Zips table (in that order) in your query. (Hint: You will have to include the Volunteers table in your query to link the VolunteerHours and Zips tables.) b. Add criteria using the In operator to select all records where the State field is equal to KS or MO. Save the query with the name KSMOVolunteerHours, switch to Datasheet View to confirm the query is similar to the one shown in Figure 4, then close the KSMOVolunteerHours query. Figure 4: KSMOVolunteerHours query Illustrated Access 2013 | Unit F: SAM Project 1a 5. Use the Crosstab Query Wizard to build a crosstab query based on the ActivityLogMicrosoft query. product screenshots used with permission from Microsoft Corporation. a. Select the ActivityDescription field for the Row headings. b. Select the WorkDate field for the column headings. c. Select Date as the interval. d. Select the Sum of the Hours field as the calculation for the query and make sure that the crosstab query will include row sums. e. Accept the default name of ActivityLog_Crosstab as the query name. Confirm the query appears as shown in Figure 5, and then close the ActivityLog_Crosstab query. Illustrated Access 2013 | Unit F: SAM Project 1a Figure 5: ActivityLog_Crosstab query 6. In Design View of the DonatedHours query, create a calculated field to the right of the ActivityDescription field named Value that multiplies the Hours product screenshots used with permission from Microsoft field by 20Microsoft to calculate the value of the donated time. (Tip: The calculated Corporation. control should have the format [Hours]*20.) Save, but do not close, the DonatedHours query. 7. In Design View of the DonatedHours query, create a second calculated field to the right of the calculated Value field named Age using the Zoom dialog box. Create the new field with the following expression that uses the Int (Integer) and Now (Today's date) functions to calculate the current age of each volunteer: Int((Now()-[Birthday])/365) Save the DonatedHours query, display it in Datasheet View as shown in Figure 6, and then close the DonatedHours query. (Tip: Depending on the date you complete this assignment, the values in the Age column in your query may not match Figure 6.) Illustrated Access 2013 | Unit F: SAM Project 1a Figure 6: DonatedHours query 8. Open the HoursbyRank query in Design view and add the Totals row to the query. The query should be grouped by the Rank field and should Sum the values in the Hours field. Save and run the query, then close the Microsoft product screenshots used with permission from Microsoft HoursbyRank query. Corporation. 9. Open the RankActivityLog query in Design view and add a sort to the query in ascending order by the ActivityDescription field and then in ascending order by the Rank field. Save and run the query, then close the RankActivityLog query. 10. Create a crosstab query in Design view based on the RankActivityLog query with the following options: a. Add the ActivityDescription, Rank, and Hours fields to the query (in that order). b. Use the ActivityDescription field as the row heading. c. Use the Rank field as the column heading. d. Use the Hours field as the value for the crosstab query, using the SUM function as the Total calculation for this field. Save the query with the name RankActivityLog_Crosstab and run it. Confirm the query looks similar to the one shown in Figure 7 and close the query. Illustrated Access 2013 | Unit F: SAM Project 1a Figure 7: RankActivityLog_Crosstab query 11. Microsoft product screenshots used with permission from Microsoft Use the Report Wizard to create a Report based on the RankActivityLog query Corporation. with the following options: a. Select the ActivityDescription, VolunteerNo, Rank, and Hours field (in that order) to the report. b. The report should be viewed by Activities without any additional grouping fields. c. The data in the report should not be sorted. d. The report should have the Stepped layout and the Portrait orientation. e. Use the name Activity Rank Report as the title for the report. f. In Layout view, resize the VolunteerNo column, Rank column, and Hours column, so that the column labels are completely visible and don't overlap. View the report and confirm it looks similar to the one shown in Figure 8-1 and Figure 8-2 then close the report. (Tip: Not all records are visible in Figure 8-1 and Figure 8-2.) Illustrated Access 2013 | Unit F: SAM Project 1a Figure 8-1: Top of Activity Rank Report Microsoft product screenshots used with permission from Microsoft Corporation. Illustrated Access 2013 | Unit F: SAM Project 1a Figure 8-2: Bottom of Activity Rank Report Save and close any open objects in your database. Compact and repair your Microsoft product screenshots used with permission from Microsoft database, close it,Corporation. and exit Access. Follow the directions on the SAM website to submit your completed project