EX16_AC_VOL1_GRADER_CAP_AS - Student Loans

Project Description:

Quill Financial Services has recently started working with student loans. You will build the companys database. The initial set of financed student loans are stored in an Excel spreadsheet. You have already compiled the list of colleges and universities into an Access table. You will use your expertise to import the information from Excel, modify the table, create relationships, create queries, create forms, and create reports.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step	Instructions	Points Possible
1	Start Access. Open the downloaded Access file named exploring_acap_grader_a1_Loans.accdb.	0
2	Import the exploring_acap_grader_a1_Clients.xlsx Excel workbook into a table named Clients. While importing the data, make sure to select the option First Row Contains Column Headings, and select ClientID as the primary key field.	10
3	After you have completed the import, open the Clients table in Design view. Change the ClientID field size to 6 and remove the @ symbol from the ClientID format property. Change the ZIP field size to 5. Change the ExpectedGraduation field to have 0 Decimal Places.	4
4	Delete the Comments field. Add a new field named LastContact as the last field in the table. Change the data type to Date/Time, and change the format to Short Date.	8
5	Switch to Datasheet View, and apply Best Fit to all columns. Sort the table on the LoanAmount field in descending order, then save and close the table.	4
6	Open the Relationships window. Add the Clients and Colleges tables to the window, and create a one-to-many relationship between the CollegeID fields in the Clients and Colleges tables. Enforce referential integrity between the two tables and select the cascade updates and cascade delete options. Save the changes, and close the Relationships window.	8
7	Create a new query using Design view. From the Clients table, add the LastName, FirstName, Email, Phone, and ExpectedGraduation fields, in that order. From the Colleges table, add the CollegeName field. Sort the query by LastName and then FirstName, both in ascending order. Set the criteria in the ExpectedGraduation field to 2019. Run the query. Save the query as 2019 Graduates and close the query.	10
8	Create a copy of the 2019 Graduates query. Name the copy Loan Payments and open the query in Design view. Remove the criteria from the ExpectedGraduation field. Create calculated field named MonthlyPayment that determines the estimated monthly student loan payment. The loan will have a fixed rate of 5% interest, paid monthly, for 10 years. Using the Pmt function, replace the rate argument with 0.05/12, the num_periods argument with 10*12, and the present_value argument with the LoanAmount field. Use 0 for the future_value and type arguments. Ensure the payment displays as a positive number. Format the field as Currency.	12
9	Run the query. Add a total row to Datasheet view. Average the MonthlyPayment field, and count the values in the LastName column. Save and close the query.	6
10	Create a new query using Design View. From the Colleges table, add the CollegeName field. From the Clients table, add the ClientID and LoanAmount fields. Display the Total row, and group by CollegeName. Show the count of ClientID and the average LoanAmount.	10
11	Change the caption for the ClientID field to Num Loans, and the caption for LoanAmount to Avg Loan. Format the LoanAmount field as Standard. Run the query. Save the query as Loan Summary by College and close it.	6
12	Create a Split Form using the Clients table as the source. Change the format of the ClientID field so the font is 18 and the font color is Dark Red (last row, first column in the Standard Colors section). Change the fill color of the ClientID field to be Black (first row, second column in the Standard Colors section). Reorder the fields in the bottom half of the split form so the FirstName displays before the LastName field. Switch to Form view and click the row for Riya Gonzalez. Change her expected graduation date to 2021. Save the form as Client Information and close it.	12
13	Create a report using the Report Wizard. From the Loan Payments query, add the LastName, FirstName, Email, ExpectedGraduation, CollegeName, and MonthlyPayment fields. Group by Colleges. Ensure the report has a Stepped layout and Landscape orientation. Save the report as Loans by College.	6
14	Apply Best Fit to all columns. Change the font size of ExpectedGraduation field values to 16. Apply Bold style to the LastName field values. Save and close the report.	4
15	Close all database objects. Close the database and then exit Access. Submit the database as directed.	0
	Total Points	100

nejju lor ngzap grode Leons.acedb (cess on Ut6 Tile Fonnaty Ascss d Copy Furnall?g All Access Obje... mSysRowld? Click to Add t Cape Community Cellage 5100 Black Hase Pik Mays LandingNI Ca330 60343-400www.atintic.od Gergen Comnunty College Berkeley College Blacmfield Collaga Broakdala CommunityCellego Caklwell University 07652 201-17-7100 03424 93-278-5400 www.berkeleycollege.edubiIR4psibz 07003 973 743 3000 www.bloomticld.cdu 4 Rifle Camp Read Wocdland Park N 045xk4JkztpnE 120Bkuie Averue P.O. Bcx 200 12-36 Callton Rd 0706 973-b18-??? www.laluwell.em! C8012 30-227-7200 www.camdencc.edu 07853 908 832-7010 07950 973-20-00nse.Pau CAMODIen County College Contenary Collegc Callp oSaint Flizaheth cuum county College of Morris CUM Cberland County Cclliczc DEOL Devry Univans ty 14 Center Grove Ruad P.O. Bcx 1500, Collcac Drte 630 U.S. Highway One ??? Madison N C8323 031 300 www.cocnl.cdu North Brunswick N Dren, ?niversity Eastwick College 08902 732-729-3360 0240 923-1-3000n 0 201-48-9400 www.eastwick.edu 001Ez County Collcgc 303 Univcrzity AvC 07666 0200 OY70? 00-338-B0www.td.ed 201-550-6000in 80x-4JZ-8422 www.gecrian.ecu Fairleigh Ditkinson untorsity Georgian Court Untversity 900 Lakewcod Ave UDDOL Hudscn County Community Collcge 70 Slp Avanue Moris Avne 07083 R-737-516 www.kpan.edu West WedsoN Mercer County Comenunty College Middlesex County Coll Monmauth Unlvaralty 1200?ld irentun "oed 2000 Wocdbridgs Ave MLRoon C8818 732348-0000 www.middlesexct.edu 07724 732-571-3400 www.monmouth.ccu 1 Normal 2039 Keedy Bouleva Unverity Helgnts 0/3 201-20-20?? ???.nicu.edu 07102 972 3000 www.nit.edu 08754 737-255-0400 ww.cooan.ecu New Jersey ty University Orean County Collaea assaic Conty Comenunity College Princeton University One Collee Blud. 08544 US-25-30O0 www.princeton.edu Princeton University G0s Ramapo valley Reud RAMDI Ramapo Cclliege of New Jarscy Raritan Valley Community Coll 11RIamington Rnad CRRDE 08-526-1700 Rowan Untversity 201 Mullicsa Hill Road ROW Rowan Colloge at BurIngton County o1 pembeton Erowna MRd Pemberton Rutgers nejju lor ngzap grode Leons.acedb (cess on Ut6 Tile Fonnaty Ascss d Copy Furnall?g All Access Obje... mSysRowld? Click to Add t Cape Community Cellage 5100 Black Hase Pik Mays LandingNI Ca330 60343-400www.atintic.od Gergen Comnunty College Berkeley College Blacmfield Collaga Broakdala CommunityCellego Caklwell University 07652 201-17-7100 03424 93-278-5400 www.berkeleycollege.edubiIR4psibz 07003 973 743 3000 www.bloomticld.cdu 4 Rifle Camp Read Wocdland Park N 045xk4JkztpnE 120Bkuie Averue P.O. Bcx 200 12-36 Callton Rd 0706 973-b18-??? www.laluwell.em! C8012 30-227-7200 www.camdencc.edu 07853 908 832-7010 07950 973-20-00nse.Pau CAMODIen County College Contenary Collegc Callp oSaint Flizaheth cuum county College of Morris CUM Cberland County Cclliczc DEOL Devry Univans ty 14 Center Grove Ruad P.O. Bcx 1500, Collcac Drte 630 U.S. Highway One ??? Madison N C8323 031 300 www.cocnl.cdu North Brunswick N Dren, ?niversity Eastwick College 08902 732-729-3360 0240 923-1-3000n 0 201-48-9400 www.eastwick.edu 001Ez County Collcgc 303 Univcrzity AvC 07666 0200 OY70? 00-338-B0www.td.ed 201-550-6000in 80x-4JZ-8422 www.gecrian.ecu Fairleigh Ditkinson untorsity Georgian Court Untversity 900 Lakewcod Ave UDDOL Hudscn County Community Collcge 70 Slp Avanue Moris Avne 07083 R-737-516 www.kpan.edu West WedsoN Mercer County Comenunty College Middlesex County Coll Monmauth Unlvaralty 1200?ld irentun "oed 2000 Wocdbridgs Ave MLRoon C8818 732348-0000 www.middlesexct.edu 07724 732-571-3400 www.monmouth.ccu 1 Normal 2039 Keedy Bouleva Unverity Helgnts 0/3 201-20-20?? ???.nicu.edu 07102 972 3000 www.nit.edu 08754 737-255-0400 ww.cooan.ecu New Jersey ty University Orean County Collaea assaic Conty Comenunity College Princeton University One Collee Blud. 08544 US-25-30O0 www.princeton.edu Princeton University G0s Ramapo valley Reud RAMDI Ramapo Cclliege of New Jarscy Raritan Valley Community Coll 11RIamington Rnad CRRDE 08-526-1700 Rowan Untversity 201 Mullicsa Hill Road ROW Rowan Colloge at BurIngton County o1 pembeton Erowna MRd Pemberton Rutgers