Solved exp19_access_app_capassessment – student loans 1.0


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.


To start, you have been provided   with a database the Information Technology department created. The database   has one table. You will be importing an Excel spreadsheet into a table and   creating a primary key. 

Start Access. Open the   downloaded Access file named Exp19_Access_Ch03_CapAssessment_Student_Loans.accdb.


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.


Now that you have imported the   data from the spreadsheet, you will modify the field properties in the   Clients table and demonstrate sorting.

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


Now that the table is imported   and modified, you will create a relationship between the Colleges and Clients   tables.

  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.


Polly Esther, a financial   adviser, would like your assistance in helping her find certain information.   You will create a query for her and demonstrate how she can change information.
  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.


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.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. Format the field as   Currency. 

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.


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.


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.


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


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 page wide (Note: it may be a number of pages long).


Save the database. Close the   database, and then exit Access. Submit the database as directed.

Essay Help
Calculate your paper price
Pages (550 words)
Approximate price: -

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!

Calculate the price of your order

Total price:

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.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.


Essay Writing Service

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.


Editing Support

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.


Revision Support

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.