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 Integer
so 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 Integer
so 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.