Information Systems for Managers
Create Query in Access
(Updated 3/11/02)
On the way to creating a report, it is useful to create a query in
Access. As was mentioned before, a report should not be just a dump of
the data in the order in which it was entered. Some massaging of the
data,
in terms of selection of records and fields, and sort order, helps to
create
a report that enlightens the reader. This massaging can be done in the
report, but it is usually easier to do it in a query. We'll leave the
report
function for formatting the data. The result of a query is a temporary
table that reflects the choices made in the design of the query. The
temporary
table is used as input for the report.
Let's assume we want a report of all open issues, sorted by date
received,
in ascending order.
To start designing a query, click on the Query button, then
double-click
on Create Query in Design view.
You will next get a panel as shown below. This allows you to pick
what
tables will supply data to the query. You can have multiple tables (and
even other queries) as sources of data.
Since we've only defined one Correspondence table, select
it,
then click on Close. The Correspondence Table with its data
elements
will appear at the top of the Query Design Screen. The bottom
portion
of the Query Design Screen will be blank until you select the data
elements
to be included in the query. Below is the screen, filled out to
reflect
the specifications discussed above.
The top part shows the tables that supply data for this query. The
lower
part is where we enter data reflecting our specification. Think of each
column below as the specification for a field in the temporary table.
We
want to work with the IDNumber, DateReceived, SenderName,
SenderOrganization,
and AssignedTo fields.
Some comments on how the query has been specified:
-
You can "drag-and-drop" fields from the upper part of the screen to the
lower part, to include them in the query.
-
See that the Sort item for DateReceived has been set to
Ascending.
This means that the resulting temporary table will be sorted according
to DateReceived, in ascending order (earlier dates first).
-
Note that the Criteria item for Closed? has been filled
out
with "=No". This means that only records with "No" in the
Closed
field will be selected.
-
Note that the "Show" box for Closed? has been unchecked. This
means
that although we are using Closed for processing, we don't necessarily
want to see it in the temporary table. Based on the criterion entered,
all records in the temporary table will have a Closed value of "No",
anyway.
Go ahead and create the query as outlined above. When finished, save
it,
giving it the name OpenMatters. If you want to get a quick view
of how the resulting dynaset will look, click on the box with the
exclamation
point at the top of the screen (this is assuming you entered some data
into the table).
OpenMatters is a fairly simple query. There are quite sophisticated
things you can do with queries, like adding or updating records in
tables,
or pulling data from multiple tables according to several criteria.
When finished, you can close the query design screen.
Next: Creating the report.