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.
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.
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.
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.
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.
You will add 10 records to the Donations table.
· a. Add the following records to the Donations table:
Date of Donation
Amount of Donation
· b. Sort the Donations table by the AmountOfDonation field in descending order. Close the table.
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.
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.
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.
Why Work with Us
Top Quality and Well-Researched Papers
We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.
Professional and Experienced Academic Writers
We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.
Free Unlimited Revisions
If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.
Prompt Delivery and 100% Money-Back-Guarantee
All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.
Original & Confidential
We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.
24/7 Customer Support
Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.
No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.
Admission Essays & Business Writing Help
An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.
Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.
If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.