A query in Access, as with any flavour of SQL, can perform
a wide variety of purposes.
A Select Query is a request to view information. The source
of most of this information is usually fields from tables that exist
in the database. The answers that are delivered by queries can take
2 tabular forms:
- a Dynaset that reflects the current state of the base tables
which it queries, and which is usually updatable. This means that
changes made to a dynaset's population are transmitted back to the
- a Snapshot which reflects a particular state of the base
tables and is non-updatable. Snapshots typically contain calculated
or grouped data.
An Action Query makes changes to the database population by
updating, deleting or adding tuples. A Data Definition Query
creates or changes the structure of the database, or database objects
contained within it.
It is possible to specify queries using either the QBE (Query By
Example) grid, using a Query Wizard or by entering standard SQL. QBE
and SQL will be described in this booklet, wizards will not due to
their 'inflexible' nature and the lack of low level control they afford.
To Create a NEW QUERY choose New from the Query Database Window (or
press the New Query Button on the Button Bar) then the New Query button.
You will be presented with a blank Select Query builder grid, and
be required to nominate tables or queries that are involved in the
query (or press Close to get past this dialog). Once in to the query
builder grid, the query can proceed manually (in SQL) or semi- automatically
using QBE. Experience shows that it is faster to write queries using
a mix of SQL & QBE
To compose a query using QBE:
- use the droplists to nominate fields you want present in the query
(in the order you want them to appear) or drag the field names from
the displayed tables into the appropriate field boxes;
- Apply Sort criteria (ascending/descending) to fields if appropriate;
- Decide if the column should be shown [X] in the answer table,
or invisible [ ] but used as a filter condition;
- Apply filter Criteria (like ="fred", or <30-Jan-1994) - you
will find that the criteria parser in Access is fairly forgiving,
and it will convert most criteria into legal expressions automatically.
To write a query using SQL press the SQL button on the Button Bar.
You will be presented with a blank SQL window. Type your query, as
you would using standard SQL.
Wildcards available for criteria in Access include ? (any single
character) and * (any sequence of characters). Logical connectives
NOT, AND, OR are available, as is the Set Operator IN.
To Execute a Query choose Query then Run from the Top Menu, or press
the Run [!] button on the Button Bar or press the Datasheet
button on the Button Bar.
To Rename a column for the purposes of display, use Select AlbName
AS Recording in SQL (or Recording: AlbName in the QBE grid).
To display only part of a field, Access provides Left(string,n),
Right(string,n) and Mid(string,start,n). In the MUSIC database for
example, if you wanted the first 3 characters of the SerNum field
from the Albums table, you could type Select Left(Albums.SerNum,3)
in SQL, or place NewTitle:Left(Albums.SerNum,3) in the Field specifier
of the QBE grid (NewTitle is a label for the new column in the answer
table and can be anything except an existing field name).
To Format the answer recordset you can specify Field Properties by
pressing the Properties button on the Button Bar after clicking in
the field to be formatted, or directly massage the Datasheet view
of the executed query. In addition, in Datasheet View, you have Font,
Row Height and Column Height control in the Format menu off the Top
To write a query that prompts for Parameters, include the
reference to the parameter (distinct from an existing field name)
as a criteria in square brackets: [Please Enter Artist:]. As an example,
a query that lists AlbName of the prompted-for Artist could be expressed
as illustrated left in QBE and right in SQL.
When run, the query places a prompt window on the screen requiring
the user to enter information and click OK (or press enter).
After the user has responded, the query delivers an answer table
in default format (i.e. with column names, records delimited by horizontal
and vertical lines, with record selectors.
A query that prompts for the first letter of Artists and displays
their Albums is as follows:
where Artist like [Please Enter Artist's First Letter:] & '*';
A query that prompts for start and finish dates and displays Albums
published between those dates is as follows:
where PDate between [Start Date:] and [Finish Date:];
To Group records either press the SUM button on the button bar, or
use a Group By clause in SQL.