Work With Tables/ Database Records - PowerPoint PPT Presentation

Work with tables database records l.jpg
Download
1 / 50

Work With Tables/ Database Records. Lesson 3. Skills Matrix. Skills Matrix. Skills Matrix. Skills Matrix. Skills Matrix. Navigate Among Records. Use the Keyboard Use the Record Navigation buttons at the bottom of the screen in Datasheet view. Navigating Among Records.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Download Presentation

Work With Tables/ Database Records

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


Work with tables database records l.jpg

Work With Tables/ Database Records

Lesson 3


Skills matrix l.jpg

Skills Matrix


Skills matrix3 l.jpg

Skills Matrix


Skills matrix4 l.jpg

Skills Matrix


Skills matrix5 l.jpg

Skills Matrix


Skills matrix6 l.jpg

Skills Matrix


Navigating among records l.jpg

Navigate Among Records

  • Use the Keyboard

  • Use the Record Navigation buttons at the bottom of the screen in Datasheet view

Navigating Among Records


Navigating among records8 l.jpg

Navigate Using the Keyboard

Navigating Among Records


Navigating among records9 l.jpg

Navigate Using the Keyboard (cont.)

Navigating Among Records


Navigating among records10 l.jpg

Navigate Using the Keyboard (cont.)

Navigating Among Records


Navigate among records l.jpg

Use the Navigation Buttons

  • Click the First, Previous, Next, Last, and New (blank) Record buttons to go to those records

  • Key a record number into the Current Record box and press Enter to go to that record

Search box

Previous record

Next record

Filter indicator

First record

Current record

Last record

New (blank) record

Navigate Among Records


Navigate among records12 l.jpg

Use the Navigation Buttons (cont.)

  • Key data into the Search box to find a match in the table

  • The Filter Indicator shows whether a filter has been applied to the table

Previous record

Next record

Filter indicator

Search box

First record

Current record

Last record

New (blank) record

Navigate Among Records


Enter edit and delete records l.jpg

Enter, Edit, and Delete Records

Enter Records

  • Position the insertion point in the cell where you want to add data and begin keying

  • In Datasheet view, enter data field by field and row by row

  • After you enter data and move to a new field, Access automatically saves the data in the table


Enter edit and delete records14 l.jpg

Enter, Edit, and Delete Records

Enter Records (cont.)

  • Each field in a table is formatted with a specific data type. You must enter that kind of data in the field.

  • Sometimes fields may contain an input mask,which is a set of placeholder characters that force you to enter data in a specific format. For example, an input mask for data might look like this: #/##/####.


Enter edit and delete records15 l.jpg

Enter, Edit, and Delete Records

Enter Records (cont.)

  • To insert a new record:

    • Select any record in the table and click the New button on the Home tab in the Records group

    • Right-click a selected record and select New Record from the shortcut menu


Enter edit and delete records16 l.jpg

Enter, Edit, and Delete Records

Edit Records

  • Select existing data to edit it

Record selector

Pencil icon indicates that the record is being edited

New record


Enter edit and delete records17 l.jpg

Enter, Edit, and Delete Records

Delete Records

  • Click the Select All button to select the entire record and then click the Delete button

Delete button and menu

Select All button


Enter edit and delete records18 l.jpg

Enter, Edit, and Delete Records

Delete Records (cont.)

  • A dialog box appears asking whether you are sure you want to delete a record

  • You cannot undo a deletion


Creating and modifying primary keys l.jpg

Creating and Modifying Primary Keys

Define and Modify a Primary Key

To define a primary key, select the row and click the Primary Key button

Key icon indicates that the field is the primary key

  • A primary key is a column that uniquely identifies each row


Creating and modifying primary keys20 l.jpg

Creating and Modifying Primary Keys

Define and Modify a Primary Key (cont.)

  • When you create a new database, Access creates a primary key field named “ID” by default and sets the data type for the field to AutoNumber

  • If you don’t have another field that would make a good primary key, you can use this field as the primary key


Creating and modifying primary keys21 l.jpg

Creating and Modifying Primary Keys

Define and Modify a Multi-field Primary Key

  • Two or more fields may be used together to provide the primary key of a table

  • Two or more primary keys in a table are called composite keys

  • In Design view, select the rows you want to designate as primary keys and click the Primary Key button


Finding and replacing data l.jpg

Finding and Replacing Data

Find and Replace Data

  • Use the Find command to search for specific text in a table or to move quickly to a particular word or number in the table

  • Use the Replace command to automatically replace a word or number with another

  • Clicking either button launches the Find and Replace dialog box


Finding and replacing data23 l.jpg

Finding and Replacing Data

Find and Replace Data (cont.)

  • Key the text or numbers that you want to search for into the Find What box and click Find Next to locate the record containing the data


Finding and replacing data24 l.jpg

Finding and Replacing Data

Find and Replace Data (cont.)

  • If you want to replace the data, key the new data into the Replace With box and click Replace or Replace All


Finding and replacing data25 l.jpg

Finding and Replacing Data

Find and Replace Data (cont.)

  • In the Match menu, you can specify where you want Access to look in a field. Select Any Part of Field for the broadest search.

  • Click the Match Case box to search for text with the same uppercase and/or lowercase capitalization of text


Finding and replacing data26 l.jpg

Finding and Replacing Data

Find and Replace Data (cont.)

  • Use wildcard characters to find words or phrases that contain specific letters or combinations of letters

    • Key a question mark (?) to represent a single character—for example, keying b?t will find bat, bet, and but

    • Key an asterisk (*) to represent a string of characters—for example, m*t will find mat, moment, or even medium format


Attaching and detaching documents l.jpg

Attaching and Detaching Documents

Attach and Detach Documents

  • Access 2007 allows you to attach documents, such as Word documents or photo files, to records in a database

  • You can only attach documents to a field that is formatted with the Attachment data type, which Access indicates with a paper clip icon

Attachments field

Number of attachments


Attaching and detaching documents28 l.jpg

Attaching and Detaching Documents

Attach and Detach Documents (cont.)

  • Double-click the record in the Attachments field to display the Attachments dialog box where you can add, remove, open, or save multiple attachments for a single record


Attaching and detaching documents29 l.jpg

Attaching and Detaching Documents

Attach and Detach Documents (cont.)

  • If the program that was used to create the attached file is installed on your computer, you can open and edit the file using that program

  • If you don’t have the program that was used to create the file, Access prompts you to choose a program you do have to view the file


Software orientation l.jpg

Software Orientation

Sort & Filter Group

Filter button

Ascending button

Selection menu

Descending button

Advanced Filter button

Clear All Sorts button

Toggle Filter button


Sort and filter data within a table l.jpg

Sort and Filter Data Within a Table

Sort Data Within a Table

  • To sort data means to arrange it alphabetically, numerically, or chronologically

  • Ascending order sorts data from beginning to end, such as from A to Z or 1 to 10

  • Descending order sorts data from end to beginning, such as from Z to A or 10 to 1


Sort and filter data within a table32 l.jpg

Sort and Filter Data Within a Table

Sort Data Within a Table (cont.)

  • To sort a column

    • Select it and click the Ascending or Descending button in the Sort & Filter group

    • Select it, right-click the column, and choose a Sort command from the shortcut menu. The available sort commands vary depending on the column’s data type.


Sort and filter data within a table33 l.jpg

Sort and Filter Data Within a Table

Sort Data Within a Table (cont.)


Sort and filter data within a table34 l.jpg

Sort and Filter Data Within a Table

Sort Data Within a Table (cont.)

  • You can sort records on multiple fields—simply decide on the order

  • The primary sort field is called the outermost field, such as Last Name. Sort this field second.

  • The secondary sort field is called an innermostfield, such as First Name. Sort this field first.


Sort and filter data within a table35 l.jpg

Sort and Filter Data Within a Table

Sort Data Within a Table (cont.)

  • Sort commands remain with the table until you remove them with the Clear All Sorts button

A sort arrow in the header row indicates that the field has been sorted


Sort and filter data within a table36 l.jpg

Sort and Filter Data Within a Table

Filter Data

  • A filter is a set of rules for determining which records will be displayed

Filter icon indicates that a filter has been applied


Sort and filter data within a table37 l.jpg

Sort and Filter Data Within a Table

Filter Data: Custom Text Filter

  • Select the field, click the Filter button, and choose a filter from the menu

  • Key text into the Custom Filter box


Sort and filter data within a table38 l.jpg

Sort and Filter Data Within a Table

Filter Data: Filter by Selection

  • Select a word in the field, right-click it to display the shortcut menu, and select a filter option from the shortcut menu


Sort and filter data within a table39 l.jpg

Sort and Filter Data Within a Table

Remove a Filter from a Field

  • Select the field that contains the filter and click the Filter button. Select Clear Filter from [field name].


Sort and filter data within a table40 l.jpg

Sort and Filter Data Within a Table

Remove all Filters

  • Click the Advanced button. Select Clear All Filters from the menu to permanently remove all filters.


Software orientation41 l.jpg

Software Orientation

Relationship Tools on the Ribbon

Removes tables and relationships from the display; it does not delete

Hides table from the display

Launches the Edit Relationships dialog box

Displays all relationships and tables in the database

Displays all the relationships and related tables for the selected table

Creates a report you can print

Launches the Show Table dialog box


Understanding table relationships l.jpg

Understanding Table Relationships

Define Table Relationships

Select a table to display in the window


Understanding table relationships43 l.jpg

Understanding Table Relationships

Define Table Relationships (cont.)

Related tables

Click Create to define the relationship

Type of relationship


Understanding table relationships44 l.jpg

Understanding Table Relationships

Define Table Relationships (cont.)

Relationship line


Understanding table relationships45 l.jpg

Understanding Table Relationships

Referential Integrity Enforced

Relationship line is thicker

Number 1 on the “one” side of the relationship

Infinity symbol on the “many” side of the relationship


Understanding table relationships46 l.jpg

Understanding Table Relationships

Print Relationship Report

  • Click the Relationship Report button to display the report. Click the Print button to print it.


Summary l.jpg

You Learned How to

  • Navigate among records

  • Enter, edit, and delete records

  • Create and modify a primary key

  • Define and modify a primary key

Summary


Summary48 l.jpg

Summary

Define and modify a multi-field primary key

Find and replace data

Attach and detach documents

Sort data within a table

You Learned How to (cont.)


Summary49 l.jpg

Summary

Filter data within a table

Remove a filter

Understand table relationships

You Learned How to (cont.)


Summary50 l.jpg

Summary

Define table relationships

Modify table relationships

Print table relationships

You Learned How to (cont.)


  • Login