INTRODUCTORY MICROSOFT ACCESS Lesson 4 Finding and Ordering Data

1
INTRODUCTORY MICROSOFT ACCESSLesson 4 Finding
and Ordering Data
2
Objectives

• Find data in a database.
• Query a database.
• Use filters.
• Sort a database.
• Index a database.
• Establish relationships in a database.
• Create a query from related tables.

3
Terms Used in This Lesson

• And operator
• Ascending sort
• Descending sort
• Filter
• Indexing
• Multitable query
• One-to-many relationship

• Or operator
• Primary key
• Query
• Referential integrity
• Relationship
• Search criteria
• Subdatasheet

4
Using Find

• The Find command is the easiest way to located
  data in a database.
• Choose Find on the Edit menu or click the Find
  button on the toolbar. The Find and Replace
  dialog box will open.

5
Creating a Query in Design View

• A query lets you combine criteria to perform
  complex searches and allows you to display
  selected fields.
• To create a query, open the database and click
  Queries on the Objects bar.
• Click New to create a new query. The New Query
  dialog box appears.
• Choose the Design View option.
• Choose the table and select the fields to be used
  in the query.
• Enter the search criteria. Save when finished.

6
Using the Simple Query Wizard

• The Simple Query Wizard asks you questions and
  then creates a query based on your answers.
• Choose Queries on the Objects bar and then click
  New.
• In the New Query dialog box, choose Simple Query
  Wizard.
• Follow the screens to create the query.

7
Using Filters

• A filter is similar to a query however, it
  displays all fields and cannot be saved.
• To create a filter, a table must be open.
• Choose Filter on the Records menu and select one
  of the Filter types
• Filter by Form
• Filter By Selection
• Filter Excluding Selection
• Advanced/Filter Sort

8
Sorting a Database

• To sort a table, open the table and place the
  insertion point in the field by which you want to
  sort.
• Click either the Sort Ascending or Sort
  Descending button.
• Ascending Sort (arranges records from A to Z or
  smallest to largest)
• Descending Sort (arranges records from Z to A or
  largest to smallest)

9
Index a Database

• Indexing is an important part of database
  management systems.
• Indexing allows records to be located quickly,
  especially in large databases.
• To index a field, go to Design view.
• Choose Yes for Indexed in the Field Properties
  section.

10
Establishing Relationships Within a Database

• You can create queries, forms, and reports to
  display information from several tables at once.
• Create a relationship between tables that contain
  a common field.
• Common fields must have the same data type
  although they can have different field names.
• Enforce referential integrity between
• tables when creating a relationship to
• ensure a valid relationship and
• prevent invalid data from being
• entered.

11
Creating a Multitable Query

• A multitable query displays the shared
  information from related tables at once.
• To create a new query, open the database, choose
  Queries on the Objects bar, and click the New
  button.
• In the New Query dialog box, choose the Design
  view option.
• Add the related tables to the query window.
• Choose the fields and enter the search criteria.
  

12
Summary

• The Find command is the easiest way to locate
  data in the database. The Find command searches
  the database for specified information.
• Queries allow more complex searches. A query
  allows you to search records using multiple and
  complex criteria and allows you to display
  selected fields.
• A filter is similar to a query however, it
  displays all fields and cannot be saved.

13
Summary

• Indexing is an important part of database
  management systems. Indexing allows records to be
  located quickly, especially in large databases.
• By defining relationships between the different
  tables within a database, you can create queries,
  forms, and reports to display information from
  several tables at once. Matching data in key
  fields sets up a relationship.