Assignment: Student Loans

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, create queries, create forms, and create reports.

Instructions

1 Start Access. Open the downloaded Access file named exploring_acap_grader_a1_Loans.accdb.

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.

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 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

15 Close all database objects. Close the database and then exit Access. Submit the database as directed.

Attachment:- Data-Files.rar