Access Lesson 7 Enhancing Table Design

1. Access Lesson 7Enhancing Table Design Microsoft Office 2010 Advanced Cable / Morrison

2. Objectives • Create an input mask. • Enter data in an input mask field. • Set validation rules. • Enter data into a field with a validation rule. • Set a lookup property. 2 2

3. Objectives (continued) • Select field values from a list. • Use the Lookup Wizard to set lookup properties. • Select data from a list with multiple columns. • Create a calculated field in a table. 3 3

4. Vocabulary • Expression Builder • expression • input mask • list box • operator • placeholder • validation rule 4 4

5. Introduction • You can add various enhancements to tables, such as: • Adding an input mask • Applying validation rules to data • Adding lookup properties to a table • Setting lookup values using the Lookup Wizard • Creating a calculated field in a table

6. Creating an Input Mask • An input maskis a pattern for common types of data entered in a field. • Access includes several input mask formats. • You may select the placeholder for your input mask field. • A placeholderappears in a field before the value is entered.

7. Creating an Input Mask (continued) • Input Mask Wizard dialog box

8. Entering Data Into an Input Mask Field • After the input mask is created, placeholders appear in the fields until the values are entered. • The format in the input mask will display as you enter new records.

9. Entering Data Into an Input Mask Field (continued) • Data entered into fields with input masks

10. Set Validation Rules • Validation rulesare rules that need to be met before the data can be entered. • The validation rule is entered as an expression in the Expression Builder. • The Expression Builderis where you type, or build, the expression. • An expressionis an arithmetic formula that performs a calculation.

11. Set Validation Rules (continued) • Examples of Expressions

12. Set Validation Rules (continued) • Expression Builder dialog box

13. Add Data Into a Field with a Validation Rule • Data entered into afield with a validation rule must meet the rule requirements. • If data is entered incorrectly, a message box will be displayed. Message box displays validation rule

14. Setting a Lookup Property • A lookup property offers a list of values that you can select. • You can select whether you want the lookup property to be in the form of: • Text box: used when you want to enter data • List box: lets you select more than one value • Combo box: lets you enter a value or select a value from a list

15. Setting a Lookup Property (continued) • Completed Lookup tab for List Box

16. Selecting Field Values From a List • After you set the lookup properties for a field, a drop-down arrow will appear in the field when it is selected. • Click the drop-down arrow to display the list. • Choose the value that you want entered into the field from the list.

17. Selecting Field Values From a List (continued) • Region field with list

18. Using the Lookup Wizard to Set Lookup Properties • To have more than one field appear in the list, you need to create lookup properties using the Lookup Wizard. First window in the Lookup Wizard dialog box

19. Using the Lookup Wizard to Set Lookup Properties (continued) • Third window in the Lookup Wizard dialog box

20. Selecting Data From a List with Multiple Columns • Using the Lookup Wizard to set lookup properties allows you to have more than one column of data appear in the list. • Having more than one column of data helps you identify the correct value to select. • Even though two columns are displayed in the list, data from only one field is entered into the field when you select an item.

21. Selecting Data From a List with Multiple Columns (continued) • List with multiple columns

22. Creating a Calculated Field in a Table • A calculated field is created using the Expression Builder. • Operators are used to create calculated fields. • Operatorsare the mathematical characters that determine the type of calculation in the expression. • Examples of operators: plus (+), minus (–), multiplication (*), and division (/)

23. Creating a Calculated Field in a Table (continued) • Completed Expression Builder dialog box

24. Summary In this lesson, you learned: • An input mask improves the accuracy of the data entered, such as automatically adding symbols like parentheses and a dash in a telephone number field. • After an input mask is created for a field, just the values need to be typed into the field, not the symbols.

25. Summary (continued) • Validation rules prevent inaccurate data from being entered into a field. • Setting a lookup property in the Lookup tab in the Field Properties pane creates a list from which values can be selected. • After lookup properties are set up for a field, you simply need to click a value from a list to select it.

26. Summary (continued) • Using the Lookup Wizard to set lookup properties allows you to have more than one column of information in the list. • A calculated field can be added to a table to perform calculations on fields in the table.