Microsoft Access 2003 Illustrated Complete

1
Microsoft Access 2003Illustrated Complete
Modifying the Database Structure
2
Objectives

• Examine relational databases
• Create related tables
• Create one-to-many relationships
• Create Lookup fields

3
Objectives

• Modify Text fields
• Modify Number and Currency fields
• Modify Date/Time fields
• Modify field validation properties

4
Examining Relational Databases

• Design each table to contain fields that describe
  only one subject
• Identify a primary key field or key field
  combination for each table
• Build one-to-many relationships between the
  tables of your database using a field common to
  each table

5
Common one-to-many relationships

• One record in the Products table is related to
  many records in the Sales table using the
  ProductID field
• One record in the Customers table is related to
  many records in the Sales table using the
  CustomerID field

6
Creating Related Tables

• The field on the one side of the relationship
  is usually the primary key field for that table
• The field on the many side of the relationship
  is called the foreign key field for that table
• The linking fields must have the same data type
  (if AutoNumber on the one side, then Number on
  the many side)

7
Creating One-to-Many Relationships
Many Table
One Table
Primary Key Field
Foreign Key Field
One-to-Many relationship type
8
Creating One-to-Many Relationships
Many Table
One Table
Primary key fields are bold
One-to-Many relationship line
9
Relationships window showsrelationships between
tables
One-to-many relationship
Linking SSN field
Linking CourseID field
10
Creating Lookup Fields

• Lookup Wizard is not a data type
• The Lookup Wizard helps you display data from a
  list or another table in place of a given field
  value
• The Lookup Wizard populates the values in the
  Lookup properties in Table Design View

11
Creating Lookup Fields
Selected field
Lookup tab shows Lookup properties
12
Sample Lookup fields
13
Modifying Text Fields

• All fields are modified in Table Design View
• Fields have properties, characteristics that
  further define them
• The properties for each field are determined by
  their data type

14
Common Text Field properties

• Field Size
• Format
• Input Mask
• Caption
• Default Value
• Required

15
Modifying Text Fields
EmployeeNo field is selected
Description of the selected property
Text data type
Field properties
16
Input Mask Property

• Provides a pattern to follow for data entry
• Defines the acceptable entries in a field
• Has three parts separated by semicolons ()
• May only be applied to Text and Date/Time fields

17
Modifying Number and Currency Fields

• Use the Number data type for fields that
  represent quantities
• Use the Currency data type for fields that
  represent money
• Currency fields are automatically formatted as
  55.99

18
Modifying Number and Currency Fields
Cost field is selected
Description of the selected property
Currency data type
Field properties
19
Common Number field properties
20
Modifying Date/Time Fields

• Use the Date/Time data type for fields that
  represent dates or times
• Use the Format property to modify the way a date
  appears
• 01/25/2006
• January 25, 2006
• 25/Jan/2006

21
Modifying Date/Time Fields
Attended field is selected
Description of the selected property
Smart Tag
Date/Time data type
Field properties
22
Smart Tags

• Automatically appear and provide a menu of
  options to help with the task at hand
• Access provides two Smart Tags
• AutoCorrect Options
• Error Indicator button

23
Modifying Field Validation Properties

• Validation Rule determines if an entry will be
  accepted
• Validation Text determines what message is
  displayed if the rule in the Validation Rule
  property evaluates false
• The Validation Text property is meaningless if a
  Validation Rule isnt entered

24
Validation Rule expressions
25
Field Validation Properties
Attended field is selected
Validation Rule and Validation Text
26
Summary

• Relational databases are created by properly
  building tables that are linked by one-to-many
  relationships
• Field properties further define a field
• Properties vary by field data type
• Modify field properties in Table Design View