4512 jamesford dr jamestown nc

Management Information System And App

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 ineffi- cient to handle in Excel. Your task will be to begin the conversion of the files front Excel to Access.

Create a New Database You need to 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. Primary and foreign keys are used to form the relationships between tables.

a. Open the a02c1Donors Excel workbook.

b. Open the a02c1Plants Excel workbook.

C. Examine the data in each worksheet and identify the col- umn that will become the primary key in an Access table. Identify the foreign keys in each table.

d. Create a new, blank database named a02c1Arbor_ LastFirst.

Create a New Table Use the new blank table created automatically by Access to hold the donations as they are received from the donors.

a. Switch to Design view and save the table as Donations.

b. Add the remaining field names in Design view. Note: The data for this table will be added later in this exercise.

• Change ID to DonationID with the AutoNurnber Data Type.

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

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

• Enter two additional fields with an appropriate data type and field properties. Hint: You need the date of donation and the amount of donation.

c. Verify the primary key is DonationID.

d. Save the table. Close the table.

Import Data from Excel You need to use the Import Spreadsheet Data Wizard twice to import a worksheet from each Excel workbook into Access. You

need to select the worksheets, specify the primary keys, set the indexing option, and name the newly imported tables (see Figures 2.12 through 2.17).

a. Click the EXTERNAL DATA tab and click Excel in the Import & Link group.

b. Locate and select the a02c1Donors workbook

c. Set the DonorID field Indexed option to Yea (No Duplicates).

d. Select DonorID as the primary key when prompted.

e. Accept the table name Donors.

f. Import the a02c1Plants file, set the ID Held as the pri- mary key, and then change the indexing option to Yes (No Duplicates).

g. Accept the table name Plants.

Ii. Open each table in Datasheet view to examine the data.

I. Change the ID field name in the Plants table to PlantID.

CreataRelationships You need to create the relationships between the tables using the Relationships window. Identify the primary key fields in each table and connect them with their foreign key counterparts in related tables. Enforce referential integrity and cascade and update related fields.

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.

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.

C. Close the open tables and open the Relationships win- dow.

d. Add the three tables to the Relationships window using the Show Table dialog box. Close the Show Tables dialog box.

e. Drag the DonorlD field in the Donors table onto the DonorID field in the Donations table. Enforce referential integrity and cascade and update related fields. Drag the PlantID field from the Plants table onto the Nunn) field of the Donations table. Enforce referential integrity and check the Cascade Update Related Fields option.

f. Close the Relationships window and save your changes.

Nair,

742 CHAPTER 2 • Capstone Exercise

Page 1