• Article

Criteria Pane (Visual Database Tools)

The Criteria pane allows you to specify query options — such as which data columns to display, how to order the results, and what rows to select — by entering your choices into a spreadsheet-like grid. In the Criteria pane you can specify the following:

  • Columns to display and column name aliases.

  • The table that a column belongs to.

  • Expressions for calculated columns.

  • The sort order for the query.

  • Search conditions.

  • Grouping criteria, including aggregate functions to use for summary reports.

  • New values for UPDATE or INSERT INTO queries.

  • Target column names for INSERT FROM queries.

Changes you make in the Criteria pane are automatically reflected in the Diagram pane and SQL pane. Similarly, the Criteria pane is updated automatically to reflect changes made in the other panes.

About the Criteria Pane

The rows in the Criteria pane display the data columns used in your query; columns in the Criteria pane display query options.

The specific information that appears in the Criteria pane depends on the type of query you are creating.

If the Criteria pane is not visible, right-click the designer, point to Pane, and then click Criteria.

Options

Column

Query type

Description

Column

All

Displays either the name of a data column used for the query or the expression for a computed column. This column is locked so that it is always visible as you scroll horizontally.

Alias

SELECT, INSERT FROM, UPDATE, MAKE TABLE

Specifies either an alternative name for a column or the name you can use for a computed column.

Table

SELECT, INSERT FROM, UPDATE, MAKE TABLE

Specifies the name of the table or table-structured object for the associated data column. This column is blank for computed columns.

Output

SELECT, INSERT FROM, MAKE TABLE

Specifies whether a data column appears in the query output.

NoteNote
If the database allows, you can use a data column for sort or search clauses without displaying it in the result set.

Sort Type

SELECT, INSERT FROM

Specifies that the associated data column is used to sort the query results and whether the sort is ascending or descending.

Sort Order

SELECT, INSERT FROM

Specifies the sort priority for data columns used to sort the result set. When you change the sort order for a data column, the sort order for all other columns is updated accordingly.

Group By

SELECT, INSERT FROM, MAKE TABLE

Specifies that the associated data column is being used to create an aggregate query. This grid column appears only if you have chosen Group By from the Tools menu or have added a GROUP BY clause to the SQL pane.

By default, the value of this column is set to Group By, and the column becomes part of the GROUP BY clause.

When you move to a cell in this column and select an aggregate function to apply to the associated data column, by default the resulting expression is added as an output column for the result set.

Criteria

All

Specifies a search condition (filter) for the associated data column. Enter an operator (the default is "=") and the value to search for. Enclose text values in single quotation marks.

If the associated data column is part of a GROUP BY clause, the expression you enter is used for a HAVING clause.

If you enter values for more than one cell in the Criteria grid column, the resulting search conditions are automatically linked with a logical AND.

To specify multiple search condition expressions for a single database column, for example, (fname > 'A') AND (fname < 'M'), add the data column to the Criteria pane twice and enter separate values in the Criteria grid column for each instance of the data column.

Or...

All

Specifies an additional search condition expression for the data column, linked to previous expressions with a logical OR. You can add more Or... grid columns by pressing the TAB key in the rightmost Or... column.

Append

INSERT FROM

Specifies the name of the target data column for the associated data column. When you create an Insert From query, the Query and View Designer attempts to match the source to an appropriate target data column. If the Query and View Designer cannot choose a match, you must provide the column name.

New Value

UPDATE, INSERT INTO

Specifies the value to place into the associated column. Enter a literal value or an expression.