Microsoft office 2007 illustrated
This presentation is the property of its rightful owner.
Sponsored Links
1 / 26

Microsoft Office 2007 - Illustrated PowerPoint PPT Presentation


  • 52 Views
  • Uploaded on
  • Presentation posted in: General

Microsoft Office 2007 - Illustrated. Modifying the Database Structure. Objectives. Examine relational databases Design related tables Create one-to-many relationships Create Lookup fields Modify Text fields (continued). Objectives (continued). Modify Number and Currency fields

Download Presentation

Microsoft Office 2007 - Illustrated

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Microsoft office 2007 illustrated

Microsoft Office 2007 - Illustrated

Modifying the Database Structure


Objectives

Objectives

  • Examine relational databases

  • Design related tables

  • Create one-to-many relationships

  • Create Lookup fields

  • Modify Text fields

    (continued)

Microsoft Office 2007-Illustrated


Objectives continued

Objectives (continued)

  • Modify Number and Currency fields

  • Modify Date/Time fields

  • Modify validation properties

  • Create Attachment fields

Microsoft Office 2007-Illustrated


Examining relational databases

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

Microsoft Office 2007-Illustrated


Common one to many relationships

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 Students table is related to many records in the Enrollments table using the StudentID field

Microsoft Office 2007-Illustrated


Designing related tables

Designing Related Tables

  • The field on the “one” side of the relationship is usually the primary key field for that table

  • A key symbol indicates the primary key field

  • The field on the “many” side of the relationship is called the foreign key field for that table

Microsoft Office 2007-Illustrated


Designing related tables1

Designing Related Tables

  • The linking fields must have the same data type (if AutoNumber on the “one” side, then Number on the “many” side)

  • A Number field used as a foreign key field must have a Long Integer Field Size property (to match the AutoNumber’s primary key field size)

Microsoft Office 2007-Illustrated


Creating one to many relationships

Creating One-to-Many Relationships

  • After creating tables, you link them together in one-to-many relationships

  • Now they are ready to use in building queries, forms or reports

Microsoft Office 2007-Illustrated


Creating one to many relationships1

Key symbol identifies

primary key field

Relationship Report button

Show Table button

One TourNo may be

sold many times

Sales table field list

One CustNo

may purchase many tours

Creating One-to-Many Relationships

Microsoft Office 2007-Illustrated


Referential integrity

Referential Integrity

  • A set of rules to ensure that no orphan records are entered or created in the database

  • Prevents deletion of a record in the “one” table that has a matching entry in the foreign key field of the “many” table

Microsoft Office 2007-Illustrated


Creating lookup fields

Creating Lookup Fields

  • Lookup Field: A field that contains Lookup properties

  • 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

Microsoft Office 2007-Illustrated


Multivalued fields

Multivalued Fields

  • Allow more than one choice from a drop-down list for a field

  • Create by using the Lookup Wizard

  • Select the Allow Multiple Values check box

Microsoft Office 2007-Illustrated


Field properties

Field Properties

  • Characteristics that apply to each field in a table

  • Examples: Field Size, Default Value, Caption

  • Input Mask Wizard property provides a visual guide for users as they enter data

Microsoft Office 2007-Illustrated


Input mask property

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

Microsoft Office 2007-Illustrated


Modifying text fields

Modifying Text Fields

Microsoft Office 2007-Illustrated


Modifying number and currency fields

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

Microsoft Office 2007-Illustrated


Modifying number and currency fields1

Currency data type

Cost field is

selected

Description of selected property

Field properties

Modifying Number and Currency Fields

Microsoft Office 2007-Illustrated


Modifying number and currency fields2

Common Number field properties

Modifying Number and Currency Fields

Microsoft Office 2007-Illustrated


Modifying date time fields

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/2008

    • January 25, 2008

    • 25/Jan/2008

Microsoft Office 2007-Illustrated


Smart tags

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

Microsoft Office 2007-Illustrated


Modifying validation properties

Modifying 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 isn’t entered

Microsoft Office 2007-Illustrated


Modifying validation properties1

Validation Rule Expressions

Modifying Validation Properties

Microsoft Office 2007-Illustrated


Creating attachment fields

Creating Attachment Fields

  • An attachment field lets you attach an external file such as:

    - Word document

    - PowerPoint presentation

    - Excel workbook

    - Image file

Microsoft Office 2007-Illustrated


Database formats

Database Formats

  • The .accdb file extension usually means the file is in Access 2007 format

  • The .mdb file extension means the file is in Access 2000 or 2002-2003

  • An .accdb file is not readable by versions earlier than Access 2007

  • In Access 2007, you can create or convert files to an earlier format

Microsoft Office 2007-Illustrated


Summary

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

Microsoft Office 2007-Illustrated


Summary1

Summary

  • You now know how to create and/or modify:

    - Related tables

    - Lookup fields

    - Text fields

    - Number and Currency fields

    - Date/Time fields

    - Attachment fields

Microsoft Office 2007-Illustrated


  • Login