New Perspectives on Microsoft Office Access 2003 Tutorial 3

1
Microsoft Office Access 2003

• Tutorial 3 Querying a Database

2
Learn how to use the Query window in Design view

• The Query window in Design view allows you to
  specify the results you want for a query.
• In the query Design view, you can specify which
  fields you want to be included in your query
  results.
• You can also 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
The Query Design view window
5
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.

6
Selecting, displaying and sorting fields
7
A sample query datasheet
8
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.

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

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

11
Using referential integrity

• When dealing with related tables, you need to
  decide if you want to enforce referential
  integrity.
• Referential integrity allows you to maintain the
  integrity between related tables.
• The rules associated with referential integrity
  specify that when you add a record to a related
  table, there must be a matching record in the
  primary table.
• If you choose to enforce referential integrity,
  you can insure that you will not have orphaned
  records (records that have no matching record in
  the primary table).

12
Use cascaded updates and cascaded deletes

• In addition to referential integrity, you can
  also tell Access to implement cascaded updates
  and cascaded deletes.
• If you choose cascaded updates, making a change
  in a field that is common to two related tables
  will cause the update to be made in both tables.
• Cascade deletes is similar. If you delete a field
  that is common to two tables, the deletion will
  take place in both tables.
• You should carefully consider whether you want to
  implement these features, as they can have
  dramatic effects on your data.

13
Selecting the tables for a relationship
14
Setting relationship options
15
The Relationships window
16
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.

17
Sort results by data type
18
Sort single or multiple fields
19
Filter data in a query

• Sometimes you may want to isolate a certain
  portion of data in your query results.
• You can set a filter, or a set of restrictions,
  to specify what records you want to view in the
  results. Only those records that match the
  criteria will display in the results datasheet.
• There are two methods used to make your
  selections Filter By Selection or Filter By
  Form.
• If you no longer want to view the data with the
  filter applied you can remove the filter.

20
Using Filter By Selection
21
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.

22
Exact match operators and results
23
Change a datasheet's appearance

• You can make several alterations to the way a
  query datasheet is displayed.
• You can change the font type, the font size, and
  the size of the columns.
• To alter the font of the datasheet
• Click Format on the menu bar
• Select Font from the dropdown menu
• In the Font dialog box, select the font you want
  and also change the size of the font
• Columns can be resized individually or you can
  select and resize all of them at one time.

24
Use a comparison operator to match a range of
values
25
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.

26
Illustration of And and Or logic
27
Creating And conditionin the design grid
28
Use multiple undo and redo

• In Access 2003, you can Undo and Redo multiple
  actions at one time.
• While working in design view for tables, queries,
  forms, and reports, you can use the undo recent
  actions button.
• The Undo button on the design toolbar has an
  associated list box. You can open the list box
  and then select the action you want to undo.
• If you undo an action and then change your mind,
  you can also redo actions by the same method,
  using the Redo button.

29
Using the Undo actions button
30
Perform calculations in a query using calculated
fields and expressions

• Queries can be built to perform a calculation as
  part of the query.
• Expressions can be entered into the query design
  grid.
• Make certain that you are following the rules of
  precedence.
• To perform a calculation in a query, you must add
  a calculated field to the query design.
• You have three options for entering expressions
• Enter the expression directly into the field text
  box
• Enter the expression in the Zoom box
• Enter the expression in the Expression Builder

31
The Expression Builder dialog box
32
A calculated field in the query datasheet
33
Using Aggregate Functions