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 database. 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

You will import two Excel workbooks into the database.

· a. Click the External Data tab and click Excel in the Import & Link group.

· b. Navigate to and select the a02c1Donors workbook to be imported.

· c. Select the First Row Contains Column Headings option.

· d. Set the DonorID field Indexed option to Yes (No Duplicates).

· e. Choose DonorID as the primary key when prompted and accept the table name Donors.

· f. Import the a02c1Plants 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 ID 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:

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

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

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

o • Add DateOfDonation as a Date/Time field.

o • Add AmountOfDonation 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 for DonorID to Long Integerso it matches the Field Size property of DonorID in the Donations table. Save and close the table.

· b. Open the Plants table in Design view and change the Field Size property for PlantID to Long Integerso it matches the Field Size property for PlantID 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

 

1

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 AmountOfDonation field in descending order. Close the table.

Use the Query Wizard

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

· a. Add the DonorID and AmountOfDonation 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 AmountOfDonation.

· 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 ENewsletter query in Design view and delete the DateofDonation 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 LastName.

· d. Run, save, and close the query. Close the database and exit Access. Based on your instructor’s directions, submit a02c1Arbor_LastFirst.