Data Files needed for this Case Problem: Redwood.accdb
Redwood Zoo The Redwood Zoo is a small zoo located in the picturesque city of Gig Harbor, Washington, on the shores of Puget Sound. The Redwood Zoo has some of the best exhibits of marine animals in the United States. The zoo relies heavily on donations to fund both permanent exhibits and temporary displays, especially those involving exotic animals. The zoo has a database to track information about donors, their pledges, and the status of funds. The zoo leaders want to find specific information about donors and their pledges. Complete the following:
1. Open the Redwood database located in the Access1\Cases folder, and then click the Enable Content button next to the Security Warning, if necessary.
2. Modify the first record in the Donor table datasheet by changing the Donor Title, Donor First Name, Donor MI, and Donor Last Name to your title, first name, middle initial, and last name. Close the table.
3. Open the Fund table, and then add the following new record to the table: Fund Code value of T09, Fund Name value of Tortoise Enclosure, and Fund Goal Amt of $8,000.
4. In the Fund table, find the record with Fund Code P11, and then change the Fund Goal Amt value to $4,500.
5. In the Fund table, find the record with Fund Code T12, and then delete the record. Close the table.
6. Create a query based on the Donor table. Include the following fields in the query, in the order shown: DonorLastName, DonorFirstName, and DonorClass. Sort the query in ascending order first on the DonorLastName field values, and then in ascending order by the DonorFirstName field values. Save the query as DonorList, and then run the query.
7. Use the DonorList query datasheet to update the Donor table by finding the record that has a DonorLastName field value of Katz, and then changing the DonorFirstName field value to Edward.
8. Change the alternate row color in the DonorList query datasheet to the Theme Color named Orange, Accent 6, Lighter 80% (row 2, column 10), and then save and close the query.
9. Use Design view to create a query based on the Donor, Pledge, and Fund tables. Select the DonorID, DonorFirstName, and DonorLastName fields from the Donor table; the PledgeNum and PledgeTotal fields from the Pledge table; and the FundName field from the Fund table. Sort the query results in descending order based on the PledgeTotal field. Select only those records with a PledgeTotal field value of greater than 200. Save the query as LargePledges, and then run the query. Use the query datasheet to calculate the average pledge amount, and then save the query.
10. Use the LargePledges query datasheet to update the Pledge table by changing the PledgeTotal field value for PledgeNum 2976 to 1,500.
11. Display Backstage view, and then save the LargePledges query as LargeCashPledges.
12. Modify the LargeCashPledges query to display only those records with pledges valuing more than $50 in cash. (Cash pledges have a PaymentMethod field value of C.) Do not include the PaymentMethod field values in the query results. Run the query, and then save and close the query.
13. Use Design view to create a query that, for all donors who pledged less than $150 or who donated to the Whale Watchers fund, displays the PledgeNum, PledgeDate, PledgeTotal, DonorFirstName, and DonorLastName fields. Do not display the FundName field. Save the query as PledgedOrWhaleWatchers, and then run and close the query.
14. In the Navigation Pane, copy the PledgedOrWhaleWatchers query, and then rename the copied query as PledgedAndWhaleWatchers.
15. Modify the PledgedAndWhaleWatchers query to select all donors who pledged less than $150 and who donated to the Whale Watchers fund. Save the revised query, and then run and close the query.
16. Use Design view to create a query that displays the DonorID (from the Donor table), PledgeTotal, PaymentMethod, PledgeDate, and FundName fields. Save the query as PledgesAfterCosts. Create a calculated field named Overhead that displays the results of multiplying the PledgeTotal field values by 15% (to account for overhead costs). Save the query, and then create a second calculated field named NetPledge that displays the results of subtracting the Overhead field values from the PledgeTotal field values. Format the calculated fields as Fixed. Display the results in ascending order by PledgeTotal. Save the modified query, and then run the query. Resize all datasheet columns to their best fit, and then save and close the query.
17, Create a query that uses the Pledge table to display the sum, average, and count of the PledgeTotal field values for all pledges. Then do the following:
a. Specify column names of Total Pledged, Average Pledge, and Number of Pledges.
b. Modify the field properties so that the values in the Total Pledged and Average Pledge columns display two decimal places and the Standard format.
c. Save the query as PledgeStatistics, run the query, resize all datasheet columns to their best fit, and then save and close the query.
d. In the Navigation Pane, create a copy of the PledgeStatistics query named PledgeStatisticsByFund.
e. Modify the PledgeStatisticsByFund query to display the sum, average, and count of the PledgeTotal field values for all pledges grouped by FundName, with FundName appearing as the first field. (Hint: Add the Fund table to the query.) Sort the records in descending order by the Total Pledged column. Save, run, and then close the query.
18. Compact and repair the Redwood database, and then close it.
Redwood Zoo The Redwood Zoo is a small zoo located in the picturesque city of Gig Harbor, Washington, on the shores of Puget Sound. The Redwood Zoo has some of the best exhibits of marine animals in the United States. The zoo relies heavily on donations to fund both permanent exhibits and temporary displays, especially those involving exotic animals. The zoo has a database to track information about donors, their pledges, and the status of funds. The zoo leaders want to find specific information about donors and their pledges. Complete the following:
1. Open the Redwood database located in the Access1\Cases folder, and then click the Enable Content button next to the Security Warning, if necessary.
2. Modify the first record in the Donor table datasheet by changing the Donor Title, Donor First Name, Donor MI, and Donor Last Name to your title, first name, middle initial, and last name. Close the table.
3. Open the Fund table, and then add the following new record to the table: Fund Code value of T09, Fund Name value of Tortoise Enclosure, and Fund Goal Amt of $8,000.
4. In the Fund table, find the record with Fund Code P11, and then change the Fund Goal Amt value to $4,500.
5. In the Fund table, find the record with Fund Code T12, and then delete the record. Close the table.
6. Create a query based on the Donor table. Include the following fields in the query, in the order shown: DonorLastName, DonorFirstName, and DonorClass. Sort the query in ascending order first on the DonorLastName field values, and then in ascending order by the DonorFirstName field values. Save the query as DonorList, and then run the query.
7. Use the DonorList query datasheet to update the Donor table by finding the record that has a DonorLastName field value of Katz, and then changing the DonorFirstName field value to Edward.
8. Change the alternate row color in the DonorList query datasheet to the Theme Color named Orange, Accent 6, Lighter 80% (row 2, column 10), and then save and close the query.
9. Use Design view to create a query based on the Donor, Pledge, and Fund tables. Select the DonorID, DonorFirstName, and DonorLastName fields from the Donor table; the PledgeNum and PledgeTotal fields from the Pledge table; and the FundName field from the Fund table. Sort the query results in descending order based on the PledgeTotal field. Select only those records with a PledgeTotal field value of greater than 200. Save the query as LargePledges, and then run the query. Use the query datasheet to calculate the average pledge amount, and then save the query.
10. Use the LargePledges query datasheet to update the Pledge table by changing the PledgeTotal field value for PledgeNum 2976 to 1,500.
11. Display Backstage view, and then save the LargePledges query as LargeCashPledges.
12. Modify the LargeCashPledges query to display only those records with pledges valuing more than $50 in cash. (Cash pledges have a PaymentMethod field value of C.) Do not include the PaymentMethod field values in the query results. Run the query, and then save and close the query.
13. Use Design view to create a query that, for all donors who pledged less than $150 or who donated to the Whale Watchers fund, displays the PledgeNum, PledgeDate, PledgeTotal, DonorFirstName, and DonorLastName fields. Do not display the FundName field. Save the query as PledgedOrWhaleWatchers, and then run and close the query.
14. In the Navigation Pane, copy the PledgedOrWhaleWatchers query, and then rename the copied query as PledgedAndWhaleWatchers.
15. Modify the PledgedAndWhaleWatchers query to select all donors who pledged less than $150 and who donated to the Whale Watchers fund. Save the revised query, and then run and close the query.
16. Use Design view to create a query that displays the DonorID (from the Donor table), PledgeTotal, PaymentMethod, PledgeDate, and FundName fields. Save the query as PledgesAfterCosts. Create a calculated field named Overhead that displays the results of multiplying the PledgeTotal field values by 15% (to account for overhead costs). Save the query, and then create a second calculated field named NetPledge that displays the results of subtracting the Overhead field values from the PledgeTotal field values. Format the calculated fields as Fixed. Display the results in ascending order by PledgeTotal. Save the modified query, and then run the query. Resize all datasheet columns to their best fit, and then save and close the query.
17, Create a query that uses the Pledge table to display the sum, average, and count of the PledgeTotal field values for all pledges. Then do the following:
a. Specify column names of Total Pledged, Average Pledge, and Number of Pledges.
b. Modify the field properties so that the values in the Total Pledged and Average Pledge columns display two decimal places and the Standard format.
c. Save the query as PledgeStatistics, run the query, resize all datasheet columns to their best fit, and then save and close the query.
d. In the Navigation Pane, create a copy of the PledgeStatistics query named PledgeStatisticsByFund.
e. Modify the PledgeStatisticsByFund query to display the sum, average, and count of the PledgeTotal field values for all pledges grouped by FundName, with FundName appearing as the first field. (Hint: Add the Fund table to the query.) Sort the records in descending order by the Total Pledged column. Save, run, and then close the query.
18. Compact and repair the Redwood database, and then close it.
