Project Description: Quill Financial Services has recently started working with student loans. You will build the company's 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, Instructions Step 6 Now that you have created the query, you will create a second query for Polly that will calculate the loan payments for which each student will be responsible (assuming monthly payments). 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.05112 , the num periods argument with 1012, and the present_value argument with the LoanAmount field. Use 0 for the future value and type arguments. Format the field as Cumency. Run the query. Ensure the payment displays as a positive number. Add a total row to Datasheet view. Average the MonthlyPayment field and count the values in the LastName column. Save and close the query. 7 Stann Dupp, the director of finance, needs to summarize information about all of the student. loans Quill Financial Services offers based on each college. You will create a totals query for him to summarize the number of loans, average loan amount by college. 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. 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. 8 Jay Walker, one of the company's administrative assistants, will handle data entry. He has asked you to simplify the way he inputs information into the Clients table. You will create a form based on the Clients table. Create a Split Form using the Clients table as the source. Change the height of all of the fields and labels to 25 collectively. 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 2022 . Save the form as Client information and close it. 9 Stann is hoping you can create a more print-friendly version of the query you created earlier for him to distribute to the executives. You will create a report based on the Loan Payments query. Create a report using the Report Wiaard. From the Loan Payments query, add the LastName, FirstName, Email, ExpectedGraduation, CollegeName, and MonthlyPayment fields. Do not add any grouping or sorting. Ensure the report is in Landscape orientation. Save the report as Loans by Client and view the report in Layout view. Adjust the width and position of the fields and labels so that all of the values are visible. Save the report. 10. Now that you have included the fields Stann has asked for, you will work to format the report to make the information more obvious. Apply the Integral theme. Group the report by the ExpectedGraduation field. Sort the records within each group by LastName then by FirstName, both in ascending order. Switch to Print: Preview mode and verify that the report is only one pege wide (Note: it may be a number of pages long). Points Step Instructions Possible 11 Save the database. Close the database, and then exit Access. Submit the database as 0 directed. Total Points 100
