Reference no: EM132401475

Capstone Exercise

The Morris Arboretum in Chestnut Hill, Pennsylvania tracks donors in Excel. They also use Excel to store a list of plants in stock. As donors contribute funds to the Arboretum, they can elect to receive a plant gift from the Arboretum. These plants are both rare plants and hard-to-find old favorites, and they are part of the annual appeal and membership drive to benefit the Arboretum's programs.

The organization has grown. and the files are too large and inefficient to handle in Excel. You will begin by importing the files from Excel into a new Access data-base. Then you will create a table to track donations, create a relationship between the two tables, and create some baseline queries.

Create a New Database

You will examine the data in the Excel worksheets to determine which fields will become the primary keys in each table and which fields will become the foreign keys.

a. Open the a02c1Donors Excel workbook, examine the data. and close the workbook.

b. Open the a02c1Plants Excel workbook. examine the data. and close the workbook.

c. Create a new. blank database named a02c1Arbor_ LastFirst. Close the new blank table created automatically by Access without saving it.

Import Data from Excel

Vou wail import rpm Excel vivirtbooks into the datahaw.

a. click the Enrrnal Data tab and click Excel in the Import & link group.

b. Navigate to and select the 40 2c 1 Donors workbook to hi imported.

c. Select the First Row Contains Column Ilcadings option.

d. Set the l)onorlD field Indexed option to Yes (No Duplicates).

e. Choose DonorH) as the primary key when prompted and accept the table name Donors.

f. Import the a02c1 Plants workbook, set the ID field as the primary key. and then change the indexing option to Yes (No Duplicates).

g. Accept the table name Plants.

h. Change the Ill field name in the Plants table to PlantID.

i. Open each table in Datasheet view to examine the data. Close the tables.

Create a New Table

You will create a new table to track the donations as they are received from the donors.

a. You will create a new table in Design view and save the table as Donations.

b. Add the following fields in Design view and set the properties as specified:

• Add the primary key field as Donation1D with the Number Data Type and a field size of Long Integer.

• Add DonorlD (a foreign key) with the Number Data Type and a field size of Long Integer.

• Add PlantID (a foreign key) as a Number and a field size of Long Integer.

• Add DtheOlDonation as a Date/Time field.

• Add AmountOIDonatton as a Currency field.

c. Switch to Datasheet view, and save the table when prompted. You will enter data into the table in a later step. Close the table.

Create Relationships

You will create the relationships between the tables using the Relationships window.

a. Open the Donors table in Design view and change the Field Size property of DonorlD to Long Integer so it matches the Field Size property of DonorlD in the Donations table. Save and close the,table.

b. Open the Plants table in Design view and change the  Field Size property for Plant ID to Long Integer so it matches the Field Size property for Plantin in the Donations table. Save and close the table.

C. Identify the primary key fields in the Donors table and the Plants table and join them with their foreign key counterparts in the related Donations table. Enforce referential integrity and cascade and update related fields. Save and close the Relationships window.

Add Sample Data to the Donations

Table

You will add 10 records to the Donations table.

a. Add the following records to the Donations table:

Donation ID Donor ID		Plant ID	Date of ' Donation	Amount of Donation
10	8228	611	3/1/2018	$150
18	5448	190	3/1/2018	$ 55
6	4091	457	3/12/2018	$125
7	11976	205	3/14/2018	$100
I	1000	25	3/17/2018	$120
12	1444	38	3/19/2018	$ 50
2	1444	38	4/3/2018	$ 50
4	10520	49	4/12/2018	$ 60
5	3072	102	4/19/2018	$ 50
21	1204	25	4/22/2018	$ 120

b. Sort the Donations table by the Amount0fDonation field in descending order. Close the table.

Use the Query Wizard

You xvill create a query of all donations greater than $100 in the Donations table.

a. Add the DonorlD and AmountOlDonation fields from Donations (in that order).

b. Save the query as Donations Over 100.

c. Add criteria to include only donations of more than $100.

d. Sort the query results in ascending order by AmountOlDonation.

e. Run the query.

f. Save and close the query.

Create a Query in Design View

You will create a query that identifies donors and donations.

a. Create a query that identifies the people who made a donation after April 1. 2018. This list will be given to the Arboretum staff so they can notify the donors that a plant is ready for pickup. The query should list the date of the donation, donor's full name (LastName  FirstName). phone number, the amount of the donation, and name of the plant they want (in that order). Add the tables and fields necessary to produce the query

b. Sort the query by date of donation in descending order. then by donor last name in ascending order.

c. Run, close, and save the query as Plant Pickup List.

Copy and Modify a Query in Design View

You will copy a query and modify it to add and sort by a different field.

a. Copy the Plant Pickup List query and paste it using ENewsletter as the query name.

b. Open the /Newsletter query in Design view and delete the DateolDonation column.

c. Add the ENewsletter field to the first column of the design grid and set it to sort in ascending order. so that the query sorts first by ENewsletter and then by Last Naive.

d. Run. save. and close the query. Close the database and exit Access. Based on your instructor's directions. submit LastFirst.

Attachment:- Data File.rar