1 / 26

Microsoft Office 2007 - Illustrated

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

enid
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Microsoft Office 2007 - Illustrated Modifying the Database Structure

  2. Objectives • Examine relational databases • Design related tables • Create one-to-many relationships • Create Lookup fields • Modify Text fields (continued) Microsoft Office 2007-Illustrated

  3. Objectives (continued) • Modify Number and Currency fields • Modify Date/Time fields • Modify validation properties • Create Attachment fields Microsoft Office 2007-Illustrated

  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 Microsoft Office 2007-Illustrated

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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. Modifying Text Fields Microsoft Office 2007-Illustrated

  16. 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

  17. Currency data type Cost field is selected Description of selected property Field properties Modifying Number and Currency Fields Microsoft Office 2007-Illustrated

  18. Common Number field properties Modifying Number and Currency Fields Microsoft Office 2007-Illustrated

  19. 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

  20. 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

  21. 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

  22. Validation Rule Expressions Modifying Validation Properties Microsoft Office 2007-Illustrated

  23. 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

  24. 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

  25. 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

  26. 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

More Related