Microsoft Access 2002

1
Microsoft Access 2002

• Tutorial 3 Querying a Database

2
The Query Window in Design View

• The Query window in Design view allows you to
• can specify which fields you want to be included
  in your query results.
• control what records are displayed in the query
  by specifying select query criteria.
• Each column in the design grid represents a field
  that will be used in the query.
• You can run the query at anytime to view the
  results according to the current specifications.

3
Add Fields To a Query in Design View

• The design grid is used to specify the fields and
  records you want to see.
• Add fields to the design grid by double-clicking
  the field in the field list or by dragging the
  field to the design grid.
• Remember that the results of a query provide a
  temporary view of the data
• The contents displayed in a query are a result of
  the fields you select and the criteria you
  specify in the query design grid
• When you use the query design window, you use
  Query By Example (QBE). When you use QBE, you
  give Access an example of the information you
  wish to see when you run your query.

4
Create, run, and save queries

• From the fields list, either drag or double-click
  fields that you want included in the query.
• A query result differs from a table's datasheet
  view in that only selected fields are displayed
• If you move a field to the design grid and then
  you want to remove it, you can click on the
  field's column and then press the delete key.
• The results of the query will be displayed in
  order by the primary key of the table unless you
  specify another sort order.
• You can save the query and give it a name by
  pressing the save button on the toolbar.

5
Update data using a query

• You can use the query datasheet to update data in
  a table.
• It is important to realize that the query
  datasheet is a temporary view of the data.
• However, when you update data by means of the
  query datasheet, the updates are placed directly
  into the underlying table.
• You can only update fields that are represented
  in the query.
• To observe the actual changes made to the table,
  close the query and open the table in datasheet
  view.

6
Access is a relational database

• A database is almost always a collection of
  tables. Access is a relational database
  management system that allows you to form
  relationships between the tables.
• When you form a relationship between tables, you
  are joining the tables.
• Tables are joined on common fields between the
  tables.
• When tables are joined, you can view data from
  both tables as if the tables were one combined
  table.

7
A one-to-many relationship

• Tables can be joined in three ways one-to-one,
  one-to-many, and many-to-many.
• A one-to-many relationship exists when one table
  has many records associated with a given value
  but the related table has only one record for
  that value.
• The table with one record is called the primary
  table
• The table with many records is called the related
  table
• Queries can be defined to use the relationship to
  extract data from both tables in a single query.

8
An example of a one-to-many relationship
The Employer table is related to the Position
table via the common field EmployerID.
The Employer table has one record for EmployerID
value 10126. The Position table has two records.
9
Query results for a one-to-many relationship
Queries can be built and saved that extract data
from both the primary table (Employer) and the
related table (Position).
10
Using referential integrity

• Referential integrity allows you to maintain the
  integrity between related tables.
• Referential integrity
• when you add a record to a related table, there
  must be a matching record in the primary table.
• Enforcing referential integrity
• insures no orphaned records (records that have no
  matching record in the primary table).

11
Cascaded Updates and Cascaded Deletes

• Cascaded updates
• change in a field that is common to two related
  tables will also cause change in the other table
• Cascaded deletes is similar.
• If you delete a field that is common to two
  tables, the deletion will take place in both
  tables.
• Be careful with these features, as they can have
  dramatic effects on your data.

12
Sort data in a query

• You can control the order of data displayed in a
  query by altering the queries sort key.
• Choose a field on which you want the data to be
  sorted and then specify whether you want the data
  sorted in ascending or descending order.
• Specify multiple fields as the sort key if you
  want to arrange data in sorted order within
  groups of data.
• Specify sort criteria for the query in the query
  design grid.

13
Sort single or multiple fields
To sort only one field, click in the desired
field to select it, and then click the Sort
Ascending or Sort Descending button on the
toolbar. To sort multiple fields, you must
specify them in the query design grid.
14
Specify an exact match condition in a query

• You can specify an exact match condition, which
  allows you to specify that only records that
  match your criteria will be displayed.
• To specify an exact match, use the criteria row
  in your query design grid.
• When you run the query after specifying an exact
  match, only those records that meet the criteria
  will be displayed.
• An exact match condition specifies that the value
  in the specified field must match the condition
  exactly.

15
Comparison Operators
16
Use the And and Or logical operators

• When you need to use multiple conditions for a
  query, you can use the logical operators to
  combine conditions.
• The And logical operator specifies that both
  conditions must be met
• The Or logical operator specifies that one or the
  other of the conditions must be met
• When you enter two conditions on the same row of
  the query design grid, an And condition is
  created.
• If you enter two conditions that are on separate
  rows, an Or condition is created.

17
Illustration of And and Or logic
The And condition.
The Or condition.
18
Creating And and Or conditions in the design grid
And conditions must be specified on the same line.
Or conditions must be specified on different
lines.