Illustrated Access 2016 | Module 6: SAM Project 1b Salvage Database IMPROVING QUERIES GETTING STARTED   Open the file IL_AC16_6b_ FirstLastName _1.accdb , available for download from the SAM website. ? Save the file as IL_AC16_6b_ FirstLastName _2.accdb by changing the “1” to a “2”. o Hint : If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program 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 Deposits table with the following options: a. Add the LocID and the Weight fields from the Deposits table (in that order) to the query. b. Add the Totals row to the query (in Design View), then sum the Weight field. c. Save the query with the name TotalsByLocID , then run the query. ( Hint : The Weight field will be renamed “SumOfWeight” when you run this query.) 2. With the TotalsByLocID query open in Datasheet View, add the Total row to the datasheet and sum the SumOfWeight field in the datasheet, as shown in Figure 1 on the next page. Save and close the TotalsByLocID query. Figure 1: TotalsByLocID Query in Datasheet View 3. Right-click the LocationDeposits query, then copy and paste using LargeRockHillDeposits as the name. 4. Open the LargeRockHillDeposits query in Design View, then add criteria to select only those records that equal Rock Hill Volunteers in the OrgName field and a Weight field value of greater than or equal to 200 . ( Hint : Records returned by this query should meet both criteria.) Switch to Datasheet View, confirm your query returns the records shown in Figure 2 below, then save and close the LargeRockHillDeposits query. Figure 2: LargeRockHillDeposits Query 5. Right-click the LocationDeposits query, then copy and paste it with the name 2020orLarge . Open the 2020orLarge query in Design View, then make the following updates: a. Add criteria to select all records with a DepositDate field value of 2020 or a Weight field value of greater than or equal to 300 . ( Hint : An asterisk should be used in place of the month and date in the DepositDate field criteria. Records returned by this query should meet one or both criteria.) b. Add two sort orders to sort the records in ascending order first by the DepositDate field value, then in ascending order by the Weight field value. Switch to viewing the 2020orLarge query in Datasheet View, confirm the query returns the records shown in Figure 3 below, then save and close the 2020orLarge query. Figure 3: 2020orLarge Query 6. Create a new query in Design View based on the Organizations and Deposits tables with the following options: a. Use the OrgName and OrgCity fields from the Organizations table, and the DepositDate and Weight fields from Deposits table (in that order) in your query. b. Add criteria using the In operator to select all records in which the OrgCity field value is equal to Destin or Seaside . c. Save the query with the name DestinSeaside , switch to Datasheet View to confirm the query matches Figure 4 below, then close the DestinSeaside query. Figure 4: DestinSeaside Query 7. Use the Crosstab Query Wizard to build a crosstab query based on the DepositsByLocationAndOrg query as described below: a. Select the OrgName field for the Row headings. b. Select the LocName field for the column headings. c. Select Weight as the calculated field. d. Select the Sum as the function. e. Enter LocationCrosstab as the query name. f. Confirm the query appears as shown in Figure 5 below, then close the LocationCrosstab query. Figure 5: LocationCrosstab Query 8. Open the DepositRevenue query in Design View, then create a calculated field to the right of the Weight field named HaulingFee that multiplies the Weight field value by 20% . ( Hint : The calculated field expression should be [Weight]*0.2 ). Save the DepositRevenue query, display it in Datasheet View— the first few records of which are shown in Figure 6 below—then close the DepositRevenue query. Figure 6: DepositRevenue Query in Datasheet View 9. Open the ServiceYears query in Design View, then create a calculated field to the right of the VolJoinDate field named ServiceYrs 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 number of years from the volunteer’s join date: Int((Now()-[VolJoinDate])/365) Save the ServiceYears query, display it in Datasheet View, as shown in Figure 7 below, then close the query. ( Hint : Depending on the date you complete this assignment, the values in the ServiceYrs column in your query may be greater than those shown in Figure 7.) Figure 7: ServiceYears Query in Datasheet View 10. Open the DepositsByOrg query in Design View, then add the Totals row to the query. The query should be grouped by the OrgName field, should count the values in the DepositDate field, and should sum the values in the Weight field. Save and run the query, then close it. 11. Open the DepositsByLocationAndOrg query in Design View, then add a sort to the query in ascending order by the LocName field, an ascending order by the OrgName field, and an ascending order by the DepositDate field. Save and run the query, then close it. 12. Create a crosstab query in Design View based on the LocationDeposits query with the following options: a. Add the LocName , OrgName , and Weight fields to the query (in that order). b. Use the LocName field as the row heading. c. Use the OrgName field as the column heading. d. Use the Weight field as the value for the crosstab query, and use the SUM function for the Total row calculation for this field. e. Save the query with the name OrgCrosstab and run it, as shown in Figure 8 on the next page, then close the query. Figure 8: OrgCrosstab Query 13. Use the Report Wizard to create a report based on the DepositsByLocationAndOrg query with the following options: a. Add the LocName , OrgName , DepositDate , and Weight fields (in that order) to the report. b. The report should be viewed by Locations without any additional grouping fields. c. The data in the report should be sorted in ascending order by the DepositDate field. d. The report should have the Stepped layout and the Portrait orientation. e. Use the name Deposits by Location as the title for the report. f. In Layout View, reposition the DepositDate column to the left approximately 0.5” as shown in Figure 9 on the next page. Save and preview the report to confirm that the first page looks similar to the one shown in Figure 9 on the next page, then close it. Figure 9: Portion of the First Page of the Deposits by Location Report Save and close any open objects in your database. Compact and repair your database, close it, then exit Access. Follow the directions on the SAM website to submit your completed project.