Queries
Queries can
be used to quickly analyze and sort information that is in an
Access database. A query allows you to present a question to your
database by specifying specific criteria.
Queries allow
you to specify:
- The table
fields that appear in a query.
- The order
of the fields in a query.
- Filter
and sort criteria for each field in a query.
Select queries
are the most commonly used type of queries in Access. They allow
you to choose which fields and records to display in a new datasheet.
Other types of queries include crosstab queries and four types
of action queries.
Queries have
two views: Design view and Datasheet view. In the Design view,
you specify which tables you want to see, which tables they come
from, and the criteria that records have to meet in order to appear
on the resulting database. Criteria are tests that records have
to pass. In the Query Datasheet view, you view the records that
are found to meet your criteria.
When you run
a query, Access pulls data out of tables and puts the data in
a database for you to see. The original table and database stay
connected, so that if you make changes to the data on your database,
the results of the query also change. When you save a query, you
save the query design, rather than the results, so that you can
ask the same questions again.
A select query
can be used to select certain data from a table or tables. It
basically filters and sorts the data and can perform simple calculations,
such as summing and averaging.
An easy way
to create a select query is to use the Simple Query Wizard. It
allows you to select the table fields you want to include in a
query. This type of query is very useful when you want to eliminate
extra fields, but still want to view every record in a database
table.
A limitation
of the Simple Query Wizard is that it does not allow you to set
sorting parameters for records in order to sort by particular
criteria, such as less than, greater than, equal to, etc. You
must use a create a query from scratch in order to be able to
set parameters.
Creating
Queries using the Query Wizard
To Create
A Query Using the Simple Query Wizard:
1. Open
your Access database. (Note: Because Access uses data from previously
created tables and queries to create new queries, the database
must contain at least one table or query before a new query
can be generated.)
2. Click
on the Queries icon in the window that appears.
3. Double-click
on Create Query by Using Wizard. The first dialog
box of the Simple Query Wizard then appears.
4. Choose
the table that contains the fields you want to select from the
Tables/Queries drop-down list.
5. Click
a field name in the Available Fields list; then click the Add
(>) button to move the field name to the Selected Fields list.
Add fields as needed, or move them all at once with the Add
All (>>) button. Click Next to move to the next screen.
6. On the
next screen, enter a title for the query. Then click Finish
to view the query results.
Notes:
Access automatically saves queries, so you do not need to
do anything special to save your query. You can rerun your
query at any time. This can give you updated results whenever
data is edited. To rerun a query: open the database; select
the Queries icon in the database window; in the Query list,
double-click the query you want to run, or click it once and
then click the Open button.
Creating
Queries from Scratch
To Create
a Query from Scratch:
Part
1: Starting a New Query
When you
create a query from scratch, you can select the tables and fields
that you use to build a query and set parameters for the fields.
1. Open
Access, then open the database that contains the table or
tables you want to use to build the query.
2. Click
the Queries icon in the window that appears.
3.Double-click
on Create Query in Design View. The Show Table dialog
box then appears, listing all of the tables in the database.
4. Click
the name of the table that contains the fields you want to
use in the query. Click the Add button. Repeat for
each table you want to add.
5. Click
Close when you finish adding tables. The Query Design
view window then opens. The tables you selected appear in
the top pane of the Query Design view. Field names will not
appear until you add them.
Query
Design View Window
Part
2: Adding Fields to a Query
The Query
Design View allows you to add the table fields you want in your
query. Be sure the tables that contain the fields you want to
use are present in the design window.
1. In
the first field column of the query grid, click in the Field
box. A drop-down arrow list then appears.
2. Click
the drop-down list and select a field. (You may need to scroll
through the list to find the field you want to use.)
3. Click
in the next field column and repeat the procedure. Repeat
to add all of the desired fields.
Note:
The order that you add fields will be the order in which
they appear in the query. If you need to change a field
that you've placed in a particular column, use the Field
drop-down list in the column to select a different field.
You may delete any field you have added to a query. Click
anywhere in the column and select Edit, then Delete Columns.
Part
3: Adding Criteria
You can
set criteria for a query that can control how field information
in selected fields appears in a completed query.
1. In
the Query Design view, click the Criteria row in the
desired field's column.
2. Type
the criteria you want to use. See the Sample Criteria for
Queries below.
Sample
Criteria for Queries
- <
(less than) - Matching values must be less than (or
before in case of dates) the specified numerical string.
- >
(greater than) - Matching values must be greater than
(or after in the case of dates) the specified numerical
string.
- <=
(less than or equal to) - Matching values must be
equal to or less than the value used in the criteria.
- >=
(greater than or equal to) - Matching values must
be equal to or greater than the value used in the criteria.
- =(equal
to) - Matching values must be equal to the criteria
string. This symbol can be used both with text and numeric
entries.
- Not
- Values matching the criteria string will not be included
in the results.
3. Queries
can contain multiple criteria. Repeat step`s 1 and 2 as needed
to add additional criteria to the field columns in the query.
Part
4: Adding Simple Calculations
You can
set up a query to do simple calculations, such as totaling information
in a specific field or averaging information. To add calculations
to a query, a Total row has to be added to the Query Design
grid. After the Total row is available, different calculations
can be chosen from a drop-down list for any of the fields that
have been selected for the query.
1. In
the Query Design view, click the Totals button on the
Query Design toolbar. A Total row is then added to the Query
Design grid (just below the Table row).
2. Click
the Total row for a field in the Query Design grid
that contains numerical data. A drop-down arrow then appears.
3. Click
the drop-down arrow to select the type of formula you want
to place in the field's Total box.
The following
are some commonly used types of formulas:
Sum
- Totals all values in the field.
Avg
- Calculates the average for all values in the field.
Min
- Displays the lowest value (the minimum) found in the
field.
Max
- Displays the highest value (the maximum) found in the
field.
Count
- Calculates the number of entries in the field (this
basically counts the entries).
StDev
- Calculates the standard deviation for the values in
the field. (This basically measures how widely the values
in the field differ from the field's average value.)
4. Repeat
steps 2 and 3 to place formulas into other field columns.
Part
5: Viewing Query Results
After you
have setup your query, you are ready to run it.
1. Click
the Save icon in the Query Design toolbar. Type in
a name for the query and then click OK.
Save
Icon
2. Click
the Run icon on the Query Design toolbar, or choose
Query, then Run. The query results then appear
in a datasheet that looks like an Access table.
Run
Icon
3. After
you have reviewed the results, click the Design View
button on the toolbar to return to the Query Design
view.
