Modifying the Database Structure

Objectives

• Examine relational databases• Design related tables• Create one-to-many relationships• Create Lookup fields• Modify Short Text fields

Microsoft Office 2013-Illustrated 2

Objectives

• Modify Number and Currency fields• Modify Date/Time fields• Modify validation properties• Create Attachment fields

Microsoft Office 2013-Illustrated 3

Examining Relational Databases

• The purpose of a relational database is to organize and store data in a way that minimizes redundancy and maximizes your flexibility when querying and analyzing data

Microsoft Office 2013-Illustrated 4

Examining Relational Databases

• To redesign a list into a relational database:• Design each table to contain fields that

describe only one subject• Identify a primary key field for each table• Build one-to-many relationships

5Microsoft Office 2013-Illustrated

Examining Relational Databases

6Microsoft Office 2013-Illustrated

Designing Related Tables

• After you develop a valid relational database design, you are ready to define the tables in Access.

• Using Table Design View, you can specify all characteristics of a table including field names, data types, field descriptions, field properties, Lookup properties, and primary key field designations.

7Microsoft Office 2013-Illustrated

Designing Related Tables

8Microsoft Office 2013-Illustrated

Creating One-to-Many Relationships

• After creating the tables you need, you link them together in appropriate one-to-many relationships using the primary key field in the “one” table and the foreign key field in the “many” table.

• To avoid rework, be sure that your table relationships are finished before building queries, forms, or reports using fields from multiple tables.

9Microsoft Office 2013-Illustrated

Creating One-to-Many Relationships

10Microsoft Office 2013-Illustrated

Creating Lookup Fields

• A Lookup field is a field that contains Lookup properties.

• Lookup properties are field properties that supply a drop-down list of values for a field.

• The values can be stored in another table or directly stored in the Row Source Lookup property of the field.

• You can set Lookup properties for a field in Table Design View using the Lookup Wizard.

11Microsoft Office 2013-Illustrated

Creating Lookup Fields

12Microsoft Office 2013-Illustrated

Modifying Short Text Fields

• Field properties are the characteristics that describe each field• Field Size• Default Value• Caption• Row Source

• These properties help ensure database accuracy and clarity

13Microsoft Office 2013-Illustrated

Modifying Short Text Fields

14Microsoft Office 2013-Illustrated

Common Short Text Field Properties

15Microsoft Office 2013-Illustrated

Modifying Number and Currency Fields

• Number and Currency fields have similar properties because they both contain numeric values.

• Currency fields store values that represent money, and Number fields store values that represent values such as quantities, measurements, and scores.

16Microsoft Office 2013-Illustrated

Common Number Field Properties

17Microsoft Office 2013-Illustrated

Modifying Date/Time Fields

• Many properties of the Date/Time field work the same way as they do in fields with a Short Text or Number data type.

• One difference, however, is the Format property, which helps you format dates in various ways such as January 25, 2013; 25-Jan-13; or 01/25/2013.

18Microsoft Office 2013-Illustrated

Modifying Validation Properties

• The Validation Rule property determines what entries a field can accept.

• You use the Validation Text property to display an explanatory message when a user tries to enter data that breaks the validation rule.

19Microsoft Office 2013-Illustrated

Validation Text Message

20Microsoft Office 2013-Illustrated

Validation Rule Expressions

21Microsoft Office 2013-Illustrated

Creating Attachment Fields

• An Attachment field allows you to attach an external file to a record.

• The Attachment data type is superior to OLE because it stores data more efficiently, stores more file formats, and requires no additional software to view the files from within Access.

22Microsoft Office 2013-Illustrated

Creating Attachment Fields

23Microsoft Office 2013-Illustrated