access lesson 7 enhancing table design
DESCRIPTION
Access Lesson 7 Enhancing Table Design. Microsoft Office 2010 Advanced. Cable / Morrison. 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. Objectives (continued). - PowerPoint PPT PresentationTRANSCRIPT
Access Lesson 7Enhancing Table Design
Microsoft Office 2010 Advanced
Cable / Morrison1
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced2
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.
22
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced3
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.
33
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced4
Vocabulary
Expression Builder expression input mask list box operator placeholder validation rule
44
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
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
5
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Creating an Input Mask
An input mask is 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 placeholder appears in a field before the
value is entered.
6
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Creating an Input Mask (continued)
Input Mask Wizard dialog box
7
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
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.
8
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Entering Data Into an Input Mask Field (continued)
Data entered into fields with input masks
9
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Set Validation Rules
Validation rules are 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 Builder is where you type, or build, the expression.
An expression is an arithmetic formula that performs a calculation.
10
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Set Validation Rules (continued)
Examples of Expressions
11
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Set Validation Rules (continued)
Expression Builder dialog box
12
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Add Data Into a Field with a Validation Rule
Data entered into a field with a validation rule must meet the rule requirements.– If data is entered incorrectly, a message box will
be displayed.
13
Message box displays validation rule
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
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
14
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Setting a Lookup Property (continued)
Completed Lookup tab for List Box
15
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
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.
16
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Selecting Field Values From a List (continued)
Region field with list
17
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
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.
18
First window in the Lookup Wizard dialog box
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Using the Lookup Wizard to Set Lookup Properties (continued)
Third window in the Lookup Wizard dialog box
19
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
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.
20
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Selecting Data From a List with Multiple Columns (continued)
List with multiple columns
21
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Creating a Calculated Field in a Table
A calculated field is created using the Expression Builder.
Operators are used to create calculated fields.– Operators are the mathematical characters that
determine the type of calculation in the expression.
– Examples of operators: plus (+), minus (–), multiplication (*), and division (/)
22
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
Creating a Calculated Field in a Table (continued)
Completed Expression Builder dialog box
23
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
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.
24
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
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.
25
Ac
ces
s L
es
son
7
Cable / Morrison Microsoft Office 2010 Advanced
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.
26