Title: New Perspectives on Microsoft Office Access 2003 Tutorial 3
1Microsoft Office Access 2003
- Tutorial 3 Querying a Database
2Learn 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.
3Add 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.
4The Query Design view window
5Create, 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.
6Selecting, displaying and sorting fields
7A sample query datasheet
8Update 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.
9Access 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.
10A 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.
11Using 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).
12Use 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.
13Selecting the tables for a relationship
14Setting relationship options
15The Relationships window
16Sort 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.
17Sort results by data type
18Sort single or multiple fields
19Filter 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.
20Using Filter By Selection
21Specify 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.
22Exact match operators and results
23Change 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.
24Use a comparison operator to match a range of
values
25Use 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.
26Illustration of And and Or logic
27Creating And conditionin the design grid
28Use 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.
29Using the Undo actions button
30Perform 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
31The Expression Builder dialog box
32A calculated field in the query datasheet
33Using Aggregate Functions