Homework Instructions

[Pages:1]Homework Instructions

1. Open the AccessAssignment file provided you to as part of this homework assignment. 2. Create a new query via Query Design.

a. Include the client's first and last name (from the Clients table) and the balance and account open date (from the Accounts table). While still in Design view, sort the query by balance in descending order.

b. Add a calculated field named AccountTime that calculates the number of days each client's accounts have been open. Assume that today's date is 12/31/2017. Keep in mind that dates must be enclosed with # symbols to denote that Access must account the information as a date.

c. Apply the General Number format (via the Properties Sheet in Desgn Mode). Save the query as Account Longevity.

d. Run the query to check the results. e. Close the query. 3. Create a new query via Query Design. a. Display the client's first name and last name (from the Clients table) and the balance

(from the Accounts table). b. Add the appropriate grouping so the client's total retirement account balances are

displayed (Hint: Add the Totals row while still in Design View). c. Sort the query such that the highest total account balances are displayed first. d. Switch to the Datasheet view. e. Add a totals row such that it will display the count of the clients as well as the

average of total account balances. f. Save the query as Total Balances By Client. g. Run the query to check the results. h. Close the query. 4. Create a copy of the Total Balances By Client query. a. Name the query Total Balances By State. b. Open the query in Design view and remove the client name from the query (first and

last). c. Group the results by the client's state. d. Run the query to check the results. e. While in Design view, sort the content by the client's state in Ascending order. f. Add criteria so clients with retirement account balances of $10,000 or more are

factored in to the query. g. Run the query to check the results. h. Save and close the query. 5. Create a new query via Query Design. a. Select the client first name, last name, and state (from the Clients table) and select the

Balance (from the Accounts table). b. Add criteria so that only customers with balances under $15,000 are displayed. c. Add a new field using the Expression Builder named LoanPayment.

i. Insert the PMT function to determine the monthly payment for a 2-year loan, paid monthly, with a 5% yearly interest rate. The present value is 25000 minus the balance. For example, if you took out a loan and had 5000 in savings, your present value would be 20000. Ensure the number displays as a positive number.

ii. Change the format of the LoanPayment field to Currency. iii. Change the caption to Loan Payment. iv. Run the query to check the results. v. Save the query as Monthly Loan Payments and close the query. 6. Close all of the database objects and submit the database to your instructor via email for review by the assignment due date.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download