Import donor data from the Excel file and name the table Donor. During the import, make sure that the table has the proper primary key and the primary key is marked correctly such as Required Yes and Indexed / No duplicates (you can also modify that after transfer)

MIS 311 Fall 2017 / Access Assignment

You work for a non-profit organization that accepts donations from individual donors and disseminates them through multiple agencies. Until now, data on donors, donations, and agencies used to be tracked in Excel files. Now, your manager asks you to create a database to store and process data.

Instructıons

Create the database and name it XYZDonationsTeamX.accdb. X should indicate the teammembers’ last names. Only the teammembers whose lastnames mentioned in the filename will get a grade.

“Donor” table:

1.     Import donor data from the Excel file and name the table Donor. During the import, make sure that the table has the proper primary key and the primary key is marked correctly such as Required Yes and Indexed / No duplicates (you can also modify that after transfer)

 

2.     Once you import the data from Excel, check the data type, mark the field properties as below:

3.     Add a new field for YearofBirth that indicates the year part of the birthdate of the donor. Assign correct data type and field size. Enter date values.

“Agency” table:

4.     Import agency data from the Excel file and name the table Agency. During the import, make sure that the table has the proper primary key and the primary key is marked correctly such as Required Yes and Indexed / No duplicates (you can also modify that after transfer)

 

5.     Once you import the data from Excel, check the data type, mark the field properties as below:

6.     Add 2 new records (meaningful not xyz or abc) to the Agency table, and then save the table.

 “Donation” table:

7.     Import donation data from the Excel file and name the table Donation. During the import, make sure that the table has the proper primary key and the primary key is marked correctly such as Required Yes and Indexed / No duplicates (you can also modify that after transfer)

 

8.     Once you import the data from Excel, check the data type, mark the field properties as below and note the foreign keys under description:

 

9.     Add below records to the Donation table and save the table. (Hint: My date settings are MM/DD/YYYY.  Yours might be DD/MM/YYYY or DD.MM.YYYY depending on how your machine was installed. Check your Access settings and enter data accordingly)

10.  Define the relationships between Agency, Donor and Donation tables (Hint: You will set only two relationships and be careful about setting it correctly). Save the changes to the Relationships window.  

 “Donations by Donor” query:

11.  Create a list of donations by donors. Display the Donor ID, First Name, and Last Name of the donor, donation date, and donation value in this given order. Sort the query results first by the donor’s last name in ascending order, the donor’s first name in ascending order, and then the donation value from smallest to largest. Save the query as Donations by Donor.

“Large Cash Donations in Q1” query:

12.  Create a list of donations that are in cash, value more than or equals to $60 and donated in the first quarter of 2010. Display the Donor ID, First Name, and Last Name of the donor, donation date and donation value in this given order. Save the query as Large Cash Donations in Q1.

 

 

 “Clothes or Junction City Donations” query:

13.  Create a list of donations of clothes or donations made in Junction City only. Display Donation ID, Donation Date, and Donation Description, Agency ID, Agency Name, City in this given order. Apply relevant criteria. Sort the query results first by City then by Donation Description in ascending order. Save the query as Clothes or Junction City Donations.

 “Donation Statistics by Agency” query:

14.  Create a list of donations by Agency. Display the sum, the average, the maximum and the count of donation value grouped by Agency Name that should appear as the first field. Rename the columns as Total Donations, Average Donation, Maximum Donation and Number of Donations. Sort the query results Total Donations in descending order. Save the query as Donation Statistics by Agency.

 “Donors without Donations” query:

15.  You will call the registered donors who did not make any donation yet. (Hint: Identify donors in the Donor table who have no matching records in the Donation table.) Display the Donor ID, Title, First Name, Last Name, and Phone. Save the query as Donors without Donations.

“Donors and Donations Report” report:

16.  Create a report of donations grouped by donors. Display the Title, First Name, and Last Name of the donor, and the Donation Date, Donation Value and Donation Description. Group on DonorID from smallest to largest. Show the sum of donation value for each donor at the group footer, and the grand total of donations at the report end. Do not show the date at the end of the report, but display page number at the end of each page. Insert a note (label) describing the report and the names of the team members (i.e. employees) who generated the report. Save the report as Donors and Donations.

“Agency Info” form:

17.  Create a form to enter modify agency records. Insert date/time, one picture / logo / image, and a note indicating that the form was designed by x,y,z. Be creative to make the form more user friendly. Save the form as Agency Info.