Create a new query using design view

Assignment Help Database Management System

Reference no: EM131919970

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

Reference no: EM131919970

Questions Cloud

Evaluate and select a security tool for recommendation : Evaluate and select a security tool for recommendation that you learned about in theiLabs modules or the EC-Council text books.

Alternatives based on incremental present worth : Compare the alternatives based on the incremental present worth (PW). Since there is positive cash flow,

Write a paper explaining what you would tell the parents : Imagine you are the guest facilitator of a parenting group. Your job is to help parents by providing information that will help them understand their children's

Create opportunities for conflict and cohesion : As presented in the readings this week, the generational mix in the workplace can create opportunities for conflict and cohesion.

Create a new query using design view : Create a new query using Design view. From the Clients table, add the LastName, FirstName, Email, Phone, and ExpectedGraduation fields, in that order.

Describe the decisions that firms must make as they consider : Describe the decisions that firms must make as they consider the steps in the New-Product Development Decision Process

Outstanding after stock repurchase is completed : How many shares of stock will be outstanding after the stock repurchase is completed?

Describe your chosen companys brand : Has this company accomplished what they were setting out to do? Have they reached the audience that they intended?

Basic equation for capital asset pricing model : basic equation for the capital asset pricing model ?(CAPM?) to work each of the following problems.

User Account

All Pages