Chapter 8 Scored

Chapter 8: PMT Function, Update Queries, Memo/Table

Objective:  Use MS Access to create a database file of information about Employees, append records from Excel file, query the data based on requested information, make a form and make a report from the data.

Start MS Access

Use the database application (MS Access) to add database validation rules and functions to the data.

• Open the Database 8SC file from the Week 10 Files.
• Save the file on your storage device as Database 8SC XX. Be sure you save to your storage location. Computer lab users make sure you save to your flash drive.

Determine Records, Fields and Objects in File

• To prepare to work in the file, review all the objects, records and fields setup in the file to understand what is included.  
  • This file has additions to it from the file used in Chapter 7
  • Some information is accurate in this file, others have been made-up for teaching concepts.  Local and sales price/list price are real figures in our area.

Create New Fields

• Create a Loan Term field, Number data type, Long Integer field size.
• Create a Loan Interest field, Number data type, Single field size, display two decimals.

Create an Update Queries

Create a new query, using the Loan Term field.  Switch to design view and specify this as an UPDATE query, then use the settings to update the field to show 30, for the criteria of Is Null (remember the field is empty, so this will populate all the records with 30).

Create a new query, using the Loan Interest field.  Switch to design view and specify this as an UPDATE query, then use the settings to update the field to show .03, for the criteria of Is Null (remember the field is empty, so this will populate all the records with 3.00%.)  (Note, if you get a different rate displayed in the table, the field settings are probably not correct).

Check the Properties table and see that both those fields are populated with the correct results now.

Create New Query

• Create a new query, called Properties Not Sold that displays the Property ID, Location, Sales Price, and Date of Sale fields.
• Switch to Design View, and using the IS Null criteria to show those properties that are still available for sale.

Create New Report

• Create a new report, using the query for Properties No Sold.  Name the report, Available Properties.  Using Layout view to arrange the fields in acceptable format, and design view to switch the theme to Retrospect.  Note:  Fieldname will be Property ID not House ID.

Calculate a Monthly Payment using a Query

• Create a new query, displaying the Property ID, Sales Price, Loan Term, Loan Interest.  Name the query Monthly Payment Query.
• Create a new field in the query design using the Build command, then Functions, Built-In Formulas, PMT function.  Carefully replace the components of the formula with correct field names and entries.
• Here is the syntax for the formula: 
  • Monthly Payment: Pmt(«rate», «num_periods», «present_value», «future_value», «type») 
  • Everything must be spelled correctly to work, including commas.
• Hints: 
• Rate is really called Loan Interest in this file, and should be in brackets.  The rate needs divided by 12 to figure monthly.   You can do that during the build of your formula.   Num Periods is really Loan Term in this file, and should be in brackets.  It needs multiplied by 12 to show the total number of payments will be made during the term of the loan.
• Present Value will be Sales Price in this file.  We are assuming full price was paid. 
• Future value which means how much is owed at end of loan which should be 0
• Type can be 0, which relates to making a payment at the beginning of the month, or later.  Both future value and type entries are optional in this formula.
• This is a tricky formula, so use Google, or YouTube to help you solve this.  Brackets, commas and values need to be in the correct format or it won't produce correct results.
• Here are the results of the query. Note:  Fieldname will be Property ID not House ID.

Create A Report

• Create a new report from the Monthly Payment Query.  Format accordingly, and apply Retrospect theme to it.

Copy Report to Word Memo

• Open the Office Memo template.  Change the guide words at the top of the memo, to include your name, and a person to cc, which means a copy goes to that person too.
• In your Access file, make sure all objects are closed.  View the Available Properties report in Layout View.
• Select the information (copy) and paste it in the Word memo.  
• Adjust the size of the pasted table, etc. for best look.
• Save the memo as Chapter 8 Memo xx.
• Results, Note:  Fieldname will be Property ID not House ID.

Save & Submit

• Submit the complete Access and Word Files.