Database Applications –

Microsoft Access

Lesson 2Modifying a Table and

Creating a Form

Updated 1/1145 slides in presentation

Lesson 2 – Tutorial 2

For Lesson 2, you will work through Tutorial 2 in your textbook.

The tutorial may expose you to more information than is identified in the course competencies.

This presentation focuses on the skills necessary to achieve the course competencies as outlined in your syllabus. Default values and validation rules are covered in this lecture, but are not discussed in the text. Pay close attention to these topics.

2

3

Foundation

In the last lesson you learned how to create a new blank database, how to create tables in Datasheet view, and how to enter records into a table. In this lesson, you will learn Overall database designHow to manage dataHow to modify the table with properties that enhance internal control and user interface

4

Guidelines for Database Design

The first step in database design is to determine the objectives (goals or purpose) of the database.• Determine the information to be stored in the

database• Organize each piece of data into its smallest useful

part - field• Group related fields into tables• Define a primary key (if appropriate) for each table• Include a common field in related tables – avoid data

redundancy (unnecessary repetition of data in multiple database tables)

5

Table Design

Once you have identified the fields and tables for your database, you can create the table.

Tables can be created in Datasheet or Design view.

Use Design view in order to have the greatest control over the table design.

6

Table Design The Table Design window is divided into two panes

The Design grid where the field name, data type, and description are entered

The Field Properties where field attributes are defined

Design Grid

Field Properties

7

Field Name Rules

Each field must have a unique name.The field name should be descriptive of

the data to be stored in the field.Field names may be up to 64 characters

long.Field names may contain any combination

of letters, numbers, and spaces but may not begin with a space.

8

Field Types

Each field must also be given a field type.

There are a total of eleven field types to choose from, but the field types we will work with most often are:

Short TextLong TextNumberCurrencyDate/TimeYes/No

9

Field Type Guidelines Short Text – choose for fields that will store any type

of textual data up to 255 characters (name, address, phone). Note-a text field will accept all other types of data (date, number, etc.).

Long Text – Choose for fields that will store long comments and explanations up to 65,535 characters

Number – choose for fields that will store numerals (not phone numbers or social security numbers).

Currency – choose for fields that will store dollar amounts.

Date/Time – choose for fields that will store dates and/or times.

Yes/No – choose for fields that will store information in a yes/no or true/false format.

10

Field Type Guidelines

The default field type is Short Text (which will accept any type of data), but it is important to specify the correct field type for each field.

When applied correctly, the field type can be part of the internal controls built into the database. By specifying the type of data to be stored in a field, you are helping to ensure that accurate data is entered into that field.

11

Field Descriptions

When entering field names and types, you also have the opportunity to enter a field description.

Field descriptions are not required but can be used as part of database documentation and user interface.

12

Take a Break

If you need to take a break, this is a good breaking point. When you return, start with slide #13.

13

Field Properties & Internal ControlTwo important features to consider when designing your database

Internal control – procedures to help protect assets and ensure accurate accounting data

User interface – the way in which the database interacts with its users

In this tutorial, we will explore how field properties help to accomplish the goals of internal control and appropriate user interface.

14

Field Properties

In addition to a name and type, each field has unique properties that further help to specify the type of data stored in the field and can also be used to apply internal controls and user interface.

While we will work with various field properties throughout this course, for this lesson we will focus on these two properties:Field SizeFormat

15

Field Size

The default field size for Short Text fields is 255.

A valid field size can be anything from 1 to 255.

The Field Size property can be an important internal control tool.

By setting a specific field size, you can help to ensure that accurate data is entered into the field.

16

Field Size Example

The following slide contains an example of how field size can help with internal control.

Remember, internal control is a feature that helps to ensure accurate data. Part of accurate data is having the data stored in the manner desired by the designer.

17

Field Size Example Assume the designer wants the State field to be

stored as the 2-character abbreviation (TN). If the field size is left at the default size of 255,

the state could be entered in any of the following ways:

TNTennTennessee

By setting the State field size to 2, you are ensuring that the state will be entered as only two characters.

18

Format Property

You will use the Format property mainly with number and date/time fields.

The format property is helpful with user interface. For instance, if you want a date displayed as a Long Date (Tuesday, January 2, 2006), the Format property will automatically display the data in that format even if the user enters the data as 1/2/06 (which is more efficient for the user.)

19

Default Field Values

Default Value Property – used to specify a value that is automatically entered in a field when a new record is created.

This feature helps with both internal control and user interface.

20

Default Field Values & Internal ControlInternal Control

The default value property assists in internal control by helping to assure that accurate data is stored in a field and is entered in the way in which the company desires.

21

Default Field Values & User InterfaceUser Interface

The default value property helps with the design of user interface by reducing time required of the user to make an entry.

22

Default Field Values

The default value is defined in the Default Value field in the Field Properties pane of Table Design.Enter the field contents exactly as you want them to be stored in the database.In the example shown, the default value for the state was entered as TN.This default value will automatically appear in the table but can be changedto any entry.

23

Validation Rules

Validation Rule Property – used to control the data that can be entered in a field by defining the input values that are allowed (valid)

Again, this feature is helpful with both internal control and user interface.

24

Validation Rules & Internal ControlInternal Control

The validation rule property helps in internal control by helping to assure that accurate data is stored in a field.

25

Validation Rules & User InterfaceUser Interface

While the validation rule property is mainly an internal control feature, it can also help with the design of user interface. Validation Rules should always be paired with Validation Text. This text allows the designer to instruct the user on acceptable entries and formats.

26

Defining Validation Rules

Enter your validation rule in the Validation Rule field of the Field Properties pane of Table Design.Validation rules may contain as many entries as you like. Each acceptable entry must be separated by the OR operator.

27

Validation Rule Example

Assume that the designer has determined that the only acceptable entries for the City field are Nashville, Memphis, or Knoxville.Enter the rule as shownwith the OR operatorbetween each acceptable entry.

Note that the text entries have quotes around them. Access will generally place these for you, but if not, you should enter them manually. The quotes distinguish the valid text entry from the database operator (Or).

28

Validation Text

Validation text is part of User Interface. When a user makes an entry that is not on a valid entry as defined by the validation rule, Access will display an error message. You can specify what you want this message to say by entering the message in the Validation Text field.Validation Rules should always be accompanied by custom Validation Text.

29

Validation Text Example

Recall from the previous example that the only acceptable entries for the City field are Nashville, Memphis, or Knoxville.Type a validation text message similar to this:

The only acceptable entries for City are Nashville, Memphis, or Knoxville.

(Note that the screenshot shown could not capture the entire validation text.)

30

Take a Break

If you need to take a break, this is a good breaking point. When you return, start with slide #31.

31

Modifying an Existing Table

Once a table has been created, you may modify any of its properties. Some property changes may affect data already entered in a table while other changes ignore data already entered.

For example, if you add a validation rule to a table after data has been entered, the validation rule applies only to new records, but does not review data already entered.

32

Modifying an Existing Table

You can also modify table design by adding, deleting, or moving fields in the table.

NOTE: If you delete a field, the field and any data entered in that field are deleted from the table.

33

Adding a Field

Add a new field by typing in the field specifications on the next blank line in the Table Design grid.

You can also place a field anywhere you would like in relation to the existing fields by inserting a blank line between any existing fields. To do this, right-click where you would like to add the field and choose Insert Rows.

34

Deleting a Field

Select the field to delete by clicking the row selector. Delete the field by pressing the Delete key on the keyboard.

RowSelector

Maintaining Data

Maintaining data refers toAdding recordsDeleting recordsUpdating records

While records can be maintained in tables, forms are often used to work more easily with selected records.

In this lesson, you will practice maintaining data in tables. In a later lesson, you will maintain data from forms (the preferred method).

35

Deleting Records – Warning!

Once a record is deleted, it cannot be restored with the Undo button.

36

37

Defining Relationships in a Database

Databases are built from tables. Good database design dictates that small collections of data should be grouped together in tables.

In order for the database to function effectively, tables within the database must be connected. This connection is formed through a common field.

38

Defining Relationships in a Database – Main Table

Each database begins with a single table. That table is often the main table. The main table is the table without which no other records would exist.

For example, it would make no sense to have a table to store grades if there were no students to whom those grades belonged. In this example, build the student table first – this is the main table.

39

Defining Relationships in a Database –Related Tables

All other tables in a database are known as related tables. In the student example, the Grade Table is a related table.

40

Defining Relationshipsin a Database

When you design the tables in your database, be sure to include a common field in each table. Note that this is necessary to create a relationship between tables and is not considered data redundancy.

In order to be a common field, the field size and type must be the same in each table. The field does not have to be named the same in each table, but it is recommended that the name be the same so that it is easy to identify the common field.

41

Defining Relationships in a Database

Relationships are defined in the Relationships window. The relationships icon is found under the Database Tools tab.

The relationships icon can also be found under the Design tab.

42

Defining Relationships in a Database

To create a relationship follow these steps:Open the relationships windowRight-click in the window and choose Show TableDouble click each table listed to add it to the Relationships window

Create the relationship by dragging a Join Line from the common field in one table to the same field in the other table (Hint – the common field is often the primary key in the main table)Choose the option to Enforce Referential Integrity – this option means that records in the main table cannot be deleted without deleting the records in the related tables

43

Defining Relationships in a Database

Your tables now look as follows in the Relationships window:

Note the join line between the tables. This join line indicates a one-to-many relationship. This is the most common type of relationship. Other relationship types are:One-to-OneMany-to-ManyUndefined

44

End of Lesson

Work through Tutorial 2 in your Access textbook.

Complete the assigned exercises following the instructions provided by your instructor on the Lesson page.