Rosemary Animal Shelter Ryan Lang needs to modify some records in the Shelter database, and then he wants to find specific information about the animals, patrons, and donations for his notfor-profit agency. Ryan asks you to help him update the database and create queries to find the information he needs. Complete the following:
1. Open the Shelter database you created and worked with in Tutorials 1 and 2, and then click the Enable Content button next to the security warning, if necessary.
2. In the Patron table, delete the record with PatronID 36064. (Hint: Delete the related record in the Donation subdatasheet first, which you need to display using the Insert Subdatasheet dialog box.) Close the Patron table without saving changes to the table layout.
3. Create a query based on the Animal table that includes the AnimalID, AnimalName, AnimalType, Gender, and ArrivalDate fields, in that order. Save the query as AnimalsByType, and then run it.
4. Modify the AnimalsByType query design so that it sorts records in ascending order first by AnimalType and then by Gender. Save and run the query.
5. In the AnimalsByType query datasheet, find the record for the animal with Animal ID D23, and then change the arrival date for this animal to 5/19/2016. Close the query.
6. Create a query that displays the PatronID, FirstName, and LastName fields from the Patron table, and the Description and DonationValue fields from the Donation table for all donations over $150. Sort the query in ascending order by donation value. Save the query as LargeDonations, run the query, and then close it.
7. Copy and paste the LargeDonations query to create a new query named LargeCashDonations.
8. Modify the LargeCashDonations query to display only those records with donations valuing more than $150 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. Create a query that displays the PatronID, FirstName, and LastName fields from the Patron table, and the AnimalName, AnimalType, Age, Gender, and Adopted fields from the Animal table. Specify that the results show records for only those animals that have been adopted. Do not display the Adopted field values in the query results. Save the query as CatAdoptions, and then run the query.
10. Filter the results of the CatAdoptions query datasheet to display records for cats only.
11. Format the datasheet of the CatAdoptions query so that it does not display gridlines, uses an alternate row Standard Color of Purple 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, if necessary. Save and close the query.
12. Create a query that displays the PatronID, FirstName, and LastName fields from the Patron table, and the Description, DonationDate, and DonationValue fields from the Donation table. Specify that the query include records for noncash donations only or for donations made in the month of June 2016. Sort the records first in ascending order by the patron’s last name, and then in descending order by the donation value. Save the query as NonCashOrJuneDonations, run the query, and then close it.
13. Copy and paste the NonCashOrJuneDonations query to create a new query named DonationsAfterStorageCharge.
14. Modify the DonationsAfterStorageCharge query so that it displays records for noncash donations made on all dates. Create a calculated field named NetDonation that displays the results of subtracting $3.50 from the DonationValue field values to account for the cost of storing each noncash donated item. Set the Caption property Net Donation for the calculated field. Display the results in ascending order by donation value. Run the query, and then modify it to format both the DonationValue field and the calculated field as Currency with two decimal places. Run the query again and resize the columns in the datasheet to their best fit, as necessary. Save and close the query.
15. Create a query based on the Donation table that displays the sum, average, and count of the DonationValue field for all donations. Then complete the following:
a. Specify field names of TotalDonations, AverageDonation, and NumberofDonations. Then specify captions to include spaces between words. b. Save the query as DonationStatistics, and then run it. Resize the query datasheet columns to their best fit. 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 again, and then save and close the query. d. Copy and paste the DonationStatistics query to create a new query named DonationStatisticsByDescription. e. Modify the DonationStatisticsByDescription query to display the sum, average, and count of the DonationValue field for all donations grouped by Description, with Description appearing as the first field. Sort the records in descending order by Total Donations. Save, run, and then close the query.
16. Compact and repair the Shelter database, and then close it.
