Chapter 7 Scored Assignment

Chapter 6: Data Validation, Functions, Charts

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 7SC file from the Week 9 Files.
• Save the file on your storage device as Database 7SC 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.

Make Field Required

• To avoid an empty field during data entry, make the Hire Date a required field.
• Use design view of the Employee Table.
• The Required field setting is default set to No, switch that field to Yes. (Design View, See the General Tab of Field)
• Switch the setting to Yes.
• Now the field cannot be left blank during entry.
• Save and close the table.

Add Validation Rule and Validation Text

• Validation rules allow for values which are or are not allowed on a specific field.
• To further prevent entries that are not valid, the user can set specific rules on fields.  The rule is a syntax string, and the text describes what must be entered in the field.
• Prevent the input of a Hire Date before today in the field.  Use the screen shot below to setup the rule which controls what is entered, and also the text prompted to the user if he/she tries to enter something not valid.
• The date function DATE()  is used, for identifying the current system date.
• The greater than or equal to symbols are dictating what can be used. No dates before today's date can be input.
• 

Input Mask Field

• An input mask is a string of characters that indicates the format of valid input values. You can use input masks in table fields, query fields, and controls on forms and reports. The input mask is stored as an object property. 
• Add a new field to the Employee Table named Phone Number.  Use short text as the data type.
• Save and close the table.
• Return to Design view of the table.
• On the new Phone Number field, set the Input Mask for a phone number that shows the area code in parenthesis.
• The input mask wizard will prompt you for setting.  Choose to store the data with the symbols.
• Save and close the table.
• Return and add three phone numbers to three employees in the list.
• Begin typing a number, the mask will format the number as a phone number.  Just type the digits needed.

Create Lookup Field

• Create a lookup field for the State field which includes these state abbreviations, CA, CO, FL , NV, OR, SD, TX (include one more of your own)
• Design View, State field, set data type to lookup wizard field.
• Type the values in yourself, using the list above + one you make up.
• Name the field State (Two Letter).
• Save the table.

Add New Employees to Table (Test New Settings)

• Add two new employees to the table, making up data consistent with previous fields, leave the Base Bonus as 0.
• Your new settings on the fields should be working.  
• These settings prevent incorrect entries into the table.
• Data validation is an important tool when design database files.

Create a Parameter Report

• Use the Parameter Hire Date query to create a new Report
• Parameter queries and report will prompt a user to input.
• Use all the fields from the Parameter Query
• Sort the Employee Last Name, then First Name field.
  • Save the report as Parameter Report
  • Run the report with the data prompt of 6/6/1988
  • Use Layout view to adjust the field location and make sure all fields are visible.

Create IIF function To Solve True or False Logic

• Create a new query called Startup List using query wizard.
• Include the First Name, Last Name fields
• Switch to Design view, and in the third grid box, right click and choose the Build command.  Widen the field too.
• Choose the Build In Functions Category, then the IIF function from the list of Functions available.

• Replace the expression placeholders  shown above with the correct syntax to show whether an employee is a StartUp Employee or a New Hire.  This is based on whether the employee received a bonus or not.
• The IIF formula is looking at logic, a statement, decides if it is true or false, then provide input based on what it determines.  
• In this case, if an employee earned a $5000 bonus, they are considered a "StartUp Employee" otherwise, they are a "New Hire".
• Run the query, and all but two employees should be listed as Startup.
• Name the query StartUp List

Create a Column Chart (Form) On Annual Salary over $100,000

• Create a new query, using the wizard displaying Employee First Name, Employee Last Name, and Annual Salary
• Save the query as Salary >$100,000
• Run the query, and switch to design view to show results for only Annual Salary Over $100,000 (>100000).
• Individuals earning over $100,000 should be listed.
• Save and close the query.

Review the Microsoft Video on Creating Charts

• The video shows the user how to make a chart from a query.
• Charts are made using the Form Design button.
• Using similar steps, create a 3D column chart, which shows the Employee Last Name, and Annual Salary fields.
  NOTE:  Steps in the video are slightly different, but work through it.  Use Google to help too if needed.
• Once built, size the chart larger, about 5 inches by 5 inches.
• Name the form, Salary Chart when finished.
• View the results of your chart in Form View.
• Your chart should display those employees who fit that criteria.

Close/Submit File

• Save the complete file.
• Submit to Canvas.