Queries

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 base tables.
• 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 Menu.

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:

		select AlbName
		from Albums
		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:

		select AlbName
		from Albums
		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.