1 / 28

Lesson 30: Maintaining a Database

Lesson 30: Maintaining a Database. Learning Objectives. After studying this lesson, you will be able to: Change the layout of a table by adjusting column width, hiding columns, and rearranging column layout Locate and update records by sorting, filtering, and using Find and Replace

aden
Download Presentation

Lesson 30: Maintaining a Database

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. Lesson 30: Maintaining a Database

  2. Learning Objectives • After studying this lesson, you will be able to: • Change the layout of a table by adjusting column width, hiding columns, and rearranging column layout • Locate and update records by sorting, filtering, and using Find and Replace • Enhance a datasheet • Set table field properties • Rename, copy, and delete database objects

  3. Formatting Datasheet Layout • Changing column width • Moving and hiding columns • Saving a datasheet layout

  4. Changing Column Width • The mouse shape is important when selecting columns and borders • Drag a column border to make the column on the left of the border wider or narrower • Double-click a column heading border to change the width of the column on the left to fit the longest data entry in the column Mouse shape on field name for selecting a column Mouse shape on column border for sizing column

  5. Moving and Hiding Columns • Hiding and moving fields in a datasheet has no impact on the actual structure of the table • Data remains available but is hidden from view when columns are hidden • Moving columns enables you to print datasheets in different arrangements Hide Fields command on context menu Black bar identifies location when dragging a field to move it

  6. Saving Datasheet Layout • Each time you make a change to the layout of a table, you must save the table • The Save button appears on the Quick Access toolbar

  7. Retrieving Data • Three basic tools: • Sorting features • Filtering tools • Find and Replace feature

  8. Sorting Records • Sort ascending • Alphabetical order from A to Z • Numeric order from lowest to highest • Chronological order from first to last • Sort descending • Reverse alphabetical order from Z to A • Numeric order from highest to lowest • Reverse chronological order from last to first

  9. Sorting Records Using Multiple Fields • Access considers second fields when values in the first field are equal • Example: Personal names • When the last name is the same, you can tell Access to consider the first name • Last name is the primary sort field • First name is the secondary sort field

  10. How Multiple Column Sorts Work • Access sorts data on multiple fields from left to right • Columns in a table must appear side by side in the datasheet • The column on the left must be the one you want sorted first (primary sort field)

  11. Using Find and Replace • Techniques similar to those in other applications • Use Find and Replace to locate records to remove or edit records

  12. Filtering Records • Filter by Selection • Selects records on the basis of the value contained in the active field for the active record • Filter by Form • Selects records on the basis of values or conditions (criteria) that you type in one or more form fields Tip! Again, Access searches only the fields you specify to find the match.

  13. Using the Toggle Filter Tool • After you apply a filter, clicking the Toggle Filter button removes the filter and displays all records • After removing a filter, clicking the Toggle Filter button reapplies the last filter applied Tip! A ToolTip displays to let you know what action you are performing: Remove Filter or Apply Filter.

  14. Identifying Comparison Operators • Identifies the comparison Access should perform • Operators include: • = • > • < • <> • >= • <=

  15. Using Wildcards • Used in place of specific characters • Primary wildcards used: • * Represents any number of characters • ? Represents an individual character • Examples: • *Graham* locates all records with graham within the text • Gra?am locates all records with gra at the beginning of the field value and am at the end of the field value with only one letter between

  16. Enhancing a Datasheet • Tools on the Home tab of the Ribbon • Gridline formatting tools • Font, font size, and font color • Table background color

  17. Modifying Table Structures • Adding fields to existing tables • Deleting fields from a table • Editing field data types

  18. Setting Up Lookup Fields • Enables you to locate data contained in a table while entering data into another table. • Lookup Wizard is a Data Type • Advantages: • Reduces the time required to enter the data repeatedly • Reduces errors associated with data entry • Restricts data to valid entries

  19. Setting Field Properties • Control the characteristics of data entered into fields • Properties available differ depending on data type of a field • Provides automatic formatting for raw data • Example: Data entered: 3185554356 Property format applied: (318) 555-4356 • Example: Data entered: la Property format applied: LA

  20. Setting Field Properties • Control the way data displays • Accounts for different format (all caps, lowercase, etc.) used by data entry clerks • Ensures consistent look in reports and other objects • Control values that can be entered • Provide tips for data entry • Set the number of characters that can be entered for a field • Set a default field value

  21. Common Field Properties Used

  22. Setting Field Size • Limits the number of characters allowed for field values • Triggers a warning message that data may be lost • Example: • Setting the State field size to 2 to accommodate state abbreviations deletes all values containing more than 2 characters in the State field

  23. Setting Captions • Changes the text that appears in datasheet column heading • Presents more descriptive field titles Fname First Name

  24. Setting Input Masks • Sets data format • Provides a consistent display of data for all records • Uses many symbols to control format • Access adds characters that control how data displays: 1234567890(123) 456-7890 howardSMITHHoward Smith

  25. Using the Input Mask Build Button

  26. Creating Validation Rules • Limits values entered into a field • Reduces errors associated with data entry • Restricts data entry to valid values • Data type should be set for the type values entered • Text provides instructions for data entry • Appears in status bar when field is active • Appears in message box when invalid values are entered • Wide variety of comparison operators • Wildcards can be used

  27. Adds a value to a field automatically Can be edited to contain a different value Setting Default Field Values

  28. Lesson 30: Maintaining a Database

More Related