Data File needed for this Case Problem: Rossi.accdb (cont. from Tutorial 2) Rossi Recy...

Data File needed for this Case Problem: Rossi.accdb (cont. from Tutorial 2)

Rossi Recycling Group Tom Rossi needs to modify some records in the Rossi database, and then he wants to find specific information about the donors, agencies, and donations to his not-for-profit agency. Tom asks you to help him update the database and create queries. Complete the following:

1. Open the Rossi database located in the Access1\Case3 folder, and then click the Enable Content button next to the Security Warning, if necessary.

2. In the Donor table, delete the record with DonorID 36065. (Hint: Delete the related record fi rst.) Close the table.

3. Create a query based on the Agency table that includes the Agency, FirstName, LastName, and City fields, in that order. Save the query as AgenciesByCity, and then run it.

4. Modify the AgenciesByCity query design so that it sorts records in ascending order first by City and then by Agency. Save and run the query.

5. In the AgenciesByCity query datasheet, change the contact for the Community Development agency to Beth Dayton. Close the query.

6. Use Design view to create a query that displays the DonorID, FirstName, and LastName fields from the Donor table, and the Description and DonationValue fields from the Donation table for all donations over $50. Sort the query in ascending order by donation value. Save the query as LargeDonations, and then run the query.

7. Display Backstage view, and then save the LargeDonations query as LargeCashDonations.

8. Modify the LargeCashDonations query to display only those records with donations valuing more than $50 in cash. Do not include the Description field values in the query results. Use the query datasheet to calculate the average cash donation. Save and close the query.

9. Use Design view to create a query that displays the AgencyID (from the Agency table), and the DonationID, DonationDate, and Description fields from the Donation table. Save the query as SeniorDonations, and then run the query.

10. Filter the results of the SeniorDonations query datasheet to display records for all donations to the SeniorCare Program (AgencyID K82).

11. Format the datasheet of the SeniorDonations query so that it does not display gridlines, uses an alternate row Standard Color of Maroon 2, and displays a font size of 12. (Hint: Use the Gridlines button in the Text Formatting group on the Home tab to select the appropriate gridlines option.) Resize the columns to display the complete field names and values. Save your changes.

12. Display Backstage view, and then save the SeniorDonations query as ComputerOrYouthDonations.

13. Modify the ComputerOrYouthDonations query to display donations of “Computer equipment” or those to the After School Youth agency (AgencyID Y68). Sort the records in ascending order first by Description and then by AgencyID. Run, save, and then close the query.

14. Use Design view to create a query (based on all three tables in the database) that displays the DonorID (Donor table), Agency, Description, and DonationValue fields for all donations that require a pickup. (Hint: The Pickup field is a Yes/No field that should not appear in the query results.) Save the query as DonationsAfterPickupCharge. Create a calculated field named Net Donation that displays the results of subtracting $8.75 from the DonationValue field values. Display the results in ascending order by donation value. Run the query, and then modify it to format the calculated field as Currency. Run the query again and resize the columns in the datasheet to their best fi t. Save and close the query.

15. Use the Donation table to display the sum, average, and count of the DonationValue field for all donations. Then complete the following:

a. Specify column names of Total Donations, Average Donation, and Number of Donations.

b. Save the query as DonationStatistics, and then run it.

c. Modify the field properties so that the values in the Total Donations and Average Donation columns display two decimal places and the Standard format. Run the query and resize the columns in the datasheet to their best fit. Save and close the query.

d. In the Navigation Pane, create a copy of the DonationStatistics query named DonationStatisticsByAgency.

e. Modify the DonationStatisticsByAgency query to display the sum, average, and count of the DonationValue field for all donations grouped by Agency, with Agency appearing as the first field. (Hint: Add the Agency table to the query.) Sort the records in descending order by Total Donations. Save, run, and then close the query.

16. Compact and repair the Rossi database, and then close it.