ac02 lectures

THE

O’L

EARY

SER

IES

© 2014 by McGraw-Hill Education. This proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

THE O’LEARY SERIES

Lab


Microsoft Access 2013 Lab 2

Modifying and Filtering a Table and Creating a Form

2

THE

O’L

EARY

SER

IES


Access Lab 2 Objectives

1.  Change field format properties 2.  Set default field values 3.  Define validation rules 4.  Hide and redisplay fields 5.  Create a lookup field 6.  Search, find and replace data

8/8/14

2

THE

O’L

EARY

SER

IES


Objectives continued…

7.  Sort records 8.  Format a datasheet 9.  Filter a table 10. Create and use a form 11. Modify the layout of a form 12. Add a record using a form

8/8/14

3

THE

O’L

EARY

SER

IES


Objectives continued…

13. Organize the Navigation Pane 14. Preview, print, close, and save a form 15.  Identify object dependencies

8/8/14

4

THE

O’L

EARY

SER

IES


Concept Preview

• Format Property • Default Value Property • Validation Rule • Expression •  Lookup Field • Find and Replace

•  Sorting • Filter • Form • Controls • Theme

8/8/14

5

THE

O’L

EARY

SER

IES


Outline of Lab 2

• Customizing Fields •  Setting Display Formats •  Setting Default Values •  Defining Validation Rules

• Hiding and Redisplaying Fields •  Hiding Field •  Redisplaying Hidden Fields

8/8/14

6

Outline continued on next slide

THE

O’L

EARY

SER

IES


Outline of Lab 2 continued…

• Creating a Lookup Field •  Using the Lookup Wizard

•  Searching for, Finding and Replacing Data •  Searching for Data •  Finding Data •  Replacing Data

8/8/14

7


THE

O’L

EARY

SER

IES



•  Sorting Records •  Sorting on a Single Field •  Sorting on Multiple Fields

• Formatting the Datasheet •  Changing Background and Gridline Color •  Changing Text Color

8/8/14

8


THE

O’L

EARY

SER

IES



• Filtering a Table •  Using Filter by Selection •  Removing and Deleting Filters •  Filtering Using Common Filters •  Filtering on Multiple Fields

8/8/14

9


THE

O’L

EARY

SER

IES



• Creating and Using Forms •  Using the Form Tool •  Using the Multiple Items Tool •  Using the Form Wizard

8/8/14


10

THE

O’L

EARY

SER

IES



• Modifying a Form •  Using Form Layout View • Moving Controls •  Applying a Layout •  Sizing and Moving Controls •  Splitting a Layout

8/8/14

11


THE

O’L

EARY

SER

IES



• Using a Form •  Navigating in Form View •  Searching in Form View •  Sorting and Filtering Data in a Form •  Adding Records Using a Form

• Organizing the Navigation Pane

8/8/14

12


THE

O’L

EARY

SER

IES



• Previewing and Printing a Form •  Printing a Selected Record

•  Identifying Object Dependencies • Review •  Key Terms •  Discussion Questions

8/8/14

13

THE

O’L

EARY

SER

IES


Customizing Fields

•  Setting Display Formats •  Setting Default Values • Defining Validation Rules

8/8/14

14

THE

O’L

EARY

SER

IES


Concept 1

• Used to specify the way numbers, dates, times, and text in a field are displayed and printed • Does not change the way Access stores data, only

the way data is displayed • Can create a custom format or choose from

predefined formats

8/8/14

15

Format Property

THE

O’L

EARY

SER

IES


Setting Display Formats

8/8/14

16

THE

O’L

EARY

SER

IES


Format Symbols for Text and Memo Data Types

Symbol Meaning Example

@ Requires a text character or space @@@-‐@@-‐@@@ would display 123456789 as 123-‐45-‐6789. Nine characters or spaces

are required.

> Forces all characters to uppercase > Would display SMITH whether you entered SMITH, smith or Smith.

< Forces all characters to lowercase < would display smith whether you entered SMITH, smith, or Smith.

& Allows an opOonal text character @@-‐@@& would display 12345 as 12-‐345 and 1234 as 1-‐234. Four out of five characters are required, and a fiSh is opOonal.

8/8/14

17

THE

O’L

EARY

SER

IES


Concept 2

• Used to specify a value when a new record is created • Used when most of the entries in a field will be the

same for the entire table • Users can accept this value or enter another value •  Saves time while entering data

8/8/14

18

Default Value Property

THE

O’L

EARY

SER

IES


Default Value Property

Change Property Value Effects of Change

8/8/14

19

THE

O’L

EARY

SER

IES


Concept 3

• Validation rules are used to control the data that can be entered in a field • Validation checks are done automatically • Validation text

• Message that appears if invalid data is entered • Can create your own message or use default message

8/8/14

20

Validation Rule

THE

O’L

EARY

SER

IES


Concept 4

• An expression is a formula consisting of symbols • Produces a single value • Create an expression by combining •  identifiers •  operators •  values

21

Expression

Expression Result

[Sales Amount] + [Sales Tax] Sums value in two fields.

“F” or “M” Restricts entry to the letters F or M only.

>= #1/1/15# AND <= #12/31/15 Restricts entries to dates greater than or equal to 1/1/15 and less than or equal to 12/31/15.

“Workout Gear” Allows the entry Workout Gear only.

SUM([Pay]) Totals the values in the Pay field.

THE

O’L

EARY

SER

IES


Comparison Operators

Operator Meaning = Equal to < > Not equal to < Less than > Greater than <= Less than or equal to >= Greater than or equal to

8/8/14

22

THE

O’L

EARY

SER

IES


Validation Rule Expression

8/8/14

23

THE

O’L

EARY

SER

IES


Validating Entries

8/8/14

24

THE

O’L

EARY

SER

IES


Hiding and Redisplaying Fields

8/8/14

25

• Hide fields in between fields that you want to see side by side • Redisplay hidden fields when finished

THE

O’L

EARY

SER

IES


Redisplaying Hidden Fields

8/8/14

26

THE

O’L

EARY

SER

IES


Concept 5

8/8/14

27

Lookup Field

•  Provides a list of values from which the user can choose to make entering data into that field •  Simpler and more accurate. •  Gets the values from an existing table or a fixed set of

values that are defined when the lookup field is created. •  Two Lookup field types:

•  Lookup list – uses another table as the source for values •  Creates a relationship between the tables

•  Value list – uses fixed values for the source values •  Used only for values that will not change often

THE

O’L

EARY

SER

IES


Creating a Lookup Field

8/8/14

28

THE

O’L

EARY

SER

IES


Entering Values to be Listed in the Lookup Field

8/8/14

29

THE

O’L

EARY

SER

IES


Lookup Tab Field Properties

8/8/14

30

THE

O’L

EARY

SER

IES


Searching for Data

8/8/14

31

THE

O’L

EARY

SER

IES


Concept 6

• Quickly finds specific information • Automatically replaces value with new information • Find Command •  Locates all specified values in a field

• Replace Command •  Finds a value and replaces it with another

8/8/14

32

Find and Replace

THE

O’L

EARY

SER

IES


Finding Data

8/8/14

33

THE

O’L

EARY

SER

IES


Find and Replace Dialog Box Options

Option Effect

Look in Searches the current field or the entire table for the specified text.

Match Locates matches to the whole field, any part of the field, or the start of the field.

Search Specifies the direction in which the table will be searched All (search all records); Down (search down from the current insertion point location in the field); or Up (search up from the current insertion point location in the field).

Match Case Find words that have the same pattern of uppercase letters as entered in the Find What text box. Using this option makes the search case sensitive.

Search Fields as Formatted

Finds data based on its display format. 8/8/14

34

THE

O’L

EARY

SER

IES


Finding Data with a Wildcard

8/8/14

35

THE

O’L

EARY

SER

IES


Using Undo

• Undo will cancel your last action as long as you have not made any further changes • For saved records, you can only undo changes to the

last edited record

8/8/14

36

THE

O’L

EARY

SER

IES


Replacing Data

8/8/14

37

THE

O’L

EARY

SER

IES


Concept 7

•  Sorting rearranges the order of the records •  Ascending order (A to Z) •  Descending order (0 – 9)

•  Sorting helps you find information quickly • Can sort by a single field or by multiple fields

8/8/14

38

Sorting

THE

O’L

EARY

SER

IES


Sorting on a Single Field

8/8/14

39

THE

O’L

EARY

SER

IES


Sorting on Multiple Fields

8/8/14

40

What are inner and outer sort fields?

THE

O’L

EARY

SER

IES


Sorting Records

8/8/14

41

THE

O’L

EARY

SER

IES


Formatting the Datasheet

• Enhance the appearance of the datasheet by applying formatting options • Can change the appearance of: • Cells • Gridlines •  Background and gridline colors •  Border and line styles

• Datasheet formats affect the entire datasheet

8/8/14

42

THE

O’L

EARY

SER

IES


Changing Background and Gridline Color

8/8/14

43

THE

O’L

EARY

SER

IES


Changing the Text Color

8/8/14

44

THE

O’L

EARY

SER

IES


Concept 8

• A restriction placed on records in order to quickly display a subset of records • Created by specifying criteria • Can be printed • Filter results cannot be saved •  Last filter criteria can be saved & reapplied •  Ideal to display the subset for a brief time and then

return to the full set of records. 8/8/14

45

Filter

THE

O’L

EARY

SER

IES


Using Filter by Selection

8/8/14

46

THE

O’L

EARY

SER

IES


Removing and Deleting Filters

8/8/14

47

THE

O’L

EARY

SER

IES


Filtering using Common Filters

•  Can perform filters on multiple criteria within a single field

8/8/14

48

THE

O’L

EARY

SER

IES


Filtering on Multiple Fields

8/8/14

49

THE

O’L

EARY

SER

IES


Creating a Simple Form

8/8/14

50

THE

O’L

EARY

SER

IES


Concept 9

• A form is a database object used to display records onscreen • Forms are based on underlying tables • Can include design control elements •  Descriptive text •  Titles & labels •  Lines, boxes, and pictures

• Forms can use calculations 8/8/14

51

Forms

THE

O’L

EARY

SER

IES


Methods for Creating Forms

Method Use to

Form tool Create a form containing all the fields in the table.

Split Form tool Create a form that displays the form and datasheet in a single window.

Blank Form tool Build a form from scratch by adding the fields you select from the table.

Datasheet tool Create a form using all the fields in the table and display it in Datasheet view.

Multiple Items tool

Create a form that displays multiple records but is more customizable than a datasheet.

Form Wizard Create a form using a wizard that guides you through the steps to create that displays selected fields, data groups, sorted records, and data from multiple tables.

Modal Dialog Create a form that must be closed before continuing with another task in the same application. 8/8/14

52

THE

O’L

EARY

SER

IES


Using the Form Tool

8/8/14

53

THE

O’L

EARY

SER

IES


Using the Multiple Items Tool

8/8/14

54

THE

O’L

EARY

SER

IES


The Form Wizard

• An easy way to create Access forms •  Select fields from underlying table • Place fields the order you want them to appear on

the form •  Fields will be in tab order

•  Select layout • Enter a name for the form

8/8/14

55

THE

O’L

EARY

SER

IES


Using the Form Wizard

8/8/14

56

THE

O’L

EARY

SER

IES


Selecting Fields

8/8/14

57

THE

O’L

EARY

SER

IES


Selecting a Form Layout

8/8/14

58

THE

O’L

EARY

SER

IES


Four Form Layouts in Form Wizard

8/8/14

59

THE

O’L

EARY

SER

IES


Form Wizard’s Finale

8/8/14

60

THE

O’L

EARY

SER

IES


Concept 10

• Controls are objects that display information, perform actions, or enhance the design of a form or report • Most common controls are:

§ Text controls – display information from the record source

§ Label controls – display descriptive labels

§ Controls can be bound or unbound 8/8/14

61

Controls

THE

O’L

EARY

SER

IES


Modifying a Form

• Use Form Layout view or Form Design view to modify the design and layout of a form • Form Design view displays the structure of the form, not the data in the form •  Used to make extensive changes to the form

• Form Layout view displays the data •  Allows you to make many basic modifications

8/8/14

62

THE

O’L

EARY

SER

IES


Using Form Layout View

8/8/14

63

THE

O’L

EARY

SER

IES


Moving Controls

• Once controls are selected, they can be moved, sized, deleted, or modified • To move a control: •  Point to a selected control •  Mouse pointer changes to •  Drag control to new location

• To make it easier to move and arrange controls, first apply the stacked layout to the controls

8/8/14

64

THE

O’L

EARY

SER

IES


Applying a Layout

8/8/14

65

THE

O’L

EARY

SER

IES


Sizing and Moving Controls in a Layout

8/8/14

66

THE

O’L

EARY

SER

IES


Splitting a Layout

8/8/14

67

THE

O’L

EARY

SER

IES


Removing Rows from a Layout

8/8/14

68

THE

O’L

EARY

SER

IES


Inserting, Merging, and Splitting Cells

8/8/14

69

THE

O’L

EARY

SER

IES


Inserting, Merging, and Splitting Cells

8/8/14

70

THE

O’L

EARY

SER

IES


Adding Existing Fields

8/8/14

71

THE

O’L

EARY

SER

IES


Concept 11

• A theme is a predefined set of formatting choices that can affect all objects in a database in one step • Access displays nine named, built-in themes •  Each theme includes two subset of themes: colors

and fonts •  Create your own custom themes by modifying an

existing theme and saving it as a custom theme 8/8/14

72

Themes

THE

O’L

EARY

SER

IES


Changing the Design Style

8/8/14

73

THE

O’L

EARY

SER

IES


Using a Form

•  Switch to Form View to utilize a form •  Can do many of the same things you can do in Datasheet

view •  Update and delete records •  Search for records •  Sort and filter data

8/8/14

74

THE

O’L

EARY

SER

IES


Navigating in Form View

8/8/14

75

THE

O’L

EARY

SER

IES


Searching in Form View

8/8/14

76

THE

O’L

EARY

SER

IES


Sorting and Filtering Data in a Form

8/8/14

77

THE

O’L

EARY

SER

IES


Adding Records Using a Form

8/8/14

78

THE

O’L

EARY

SER

IES


Organizing the Navigation Pane

8/8/14

79

THE

O’L

EARY

SER

IES


Previewing and Printing a Form

8/8/14

80

THE

O’L

EARY

SER

IES


Printing a Selected Record

8/8/14

81

THE

O’L

EARY

SER

IES


Identifying Object Dependencies

• Many Access objects are dependent on other objects •  A form is dependent on a database table

•  The table provides the content data

• Helpful to find out what objects an object is dependent on or what depend on it

8/8/14

82

THE

O’L

EARY

SER

IES


Using Database Tools to Find Dependencies

8/8/14

83

THE

O’L

EARY

SER

IES


Key Terms

•  argument •  ascending sort order •  attachment control •  bound control •  character string •  comparison operator •  constant •  control

•  criteria •  Default Value property •  descending sort order •  expression •  filter •  Find and Replace •  form •  format

8/8/14

84

THE

O’L

EARY

SER

IES


Key Terms continued…

•  Format property •  Form Wizard •  function •  identifier •  IntelliSense •  label control •  layout •  lookup field

•  lookup list •  Lookup Wizard • merge cells •  object dependencies •  operator •  outer sort field •  record source •  sorting

8/8/14

85

THE

O’L

EARY

SER

IES


Key Terms continued…

•  split cells •  stacked format •  tab order •  tabular format •  text control •  theme •  theme colors •  unbound control

•  validation rule •  Validation Rule property •  validation text •  Validation Text property •  value list • wildcards • wizard

8/8/14

86

THE

O’L

EARY

SER

IES


Discussion Questions

1.  Discuss several different format properties and how they are used in a database.

2.  Discuss the different types of form layouts and why you would use one layout type over another.

8/8/14

87

THE

O’L

EARY

SER

IES


Discussion Questions

3.  Discuss how validity checks work. What are some advantages of adding validity checks to a field? Include several examples.

4.  What is the difference between bound and unbound controls? Explain.

5.  Explain object dependencies. Why are they important in Access?

8/8/14

88

THE

O’L

EARY

SER

IES

© 2014 by McGraw-Hill Education. This proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part. © 2014 by McGraw-Hill Education. This proprietary material solely for authorized instructor use. Not authorized for sale or distribution in any manner. This document may not be copied, scanned, duplicated, forwarded, distributed, or posted on a website, in whole or part.

Access Lab 2

89