The Grid 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 Grid pane you can specify:
Changes you make in the Grid pane are automatically reflected in the Diagram pane and SQL pane. Similarly, the Grid pane is updated automatically to reflect changes made in the other panes.
In this topic, you can find information about:
The rows in the Grid pane display the data columns used in your query; columns in the Grid pane display query options.
The specific information that appears in the Grid pane depends on the type of query you are creating. If you are creating a Select query, the Grid pane contains different columns than if you are creating an Update query.
The following table lists the grid columns that can appear in the Grid pane.
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, Update, Make Table | Specifies either an alternate name for a column or the name you can use for a computed column. |
Table | Select, Insert, Update, Make Table | Specifies the name of the input source for the associated data column. This column is blank for computed columns. |
Output | Select, Insert, Make Table | Specifies whether a data column appears in the query output. Note 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 | 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 | 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, 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, |
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 | Specifies the name of the target data column for the associated data column. When you create an Insert query, the Query Designer attempts to match the source to an appropriate target data column. If the Query Designer cannot choose a match, you must provide the column name. |
New Value | Update, Insert Values | Specifies the value to place into the associated column. Enter a literal value or an expression. |
You can move from cell to cell in the Grid pane using the mouse or by pressing the TAB or arrow keys. As you scroll, the leftmost grid column (Column) is locked so that you can always see the data column name you are working with.
You can resize grid columns to make more room for entries.
To resize a grid column
You can specify query options by entering values in the appropriate cell of the Grid pane. Certain grid columns for example, Column or Sort Type display drop-down lists when you navigate to them.
The check box in the Output grid column indicates whether a data column appears in the result set of the query. If this check box is selected, the data column will appear in the output. If it is not selected, the data column will be used for ordering or searching, but will not appear in the result set.
To remove a query option
You can also remove all values in the Grid column at once.
To remove all the values in a Grid column
You can add and remove data columns to the grid to make them part of the query. If you are displaying data columns using a Select query, the order in which you add them to the query determines the order they appear in the Results pane. Adding a data column to the query puts it rightmost in the Results pane.
If you are creating an Update, Insert, or Make Table query, the order in which you add data columns dictates the order in which the data is processed.
To control more precisely where a data column appears in the output or is used in queries, you can insert a data column between existing grid columns.
To add a data column to the grid
or
To insert a data column in the grid
To remove a data column from the grid
The Query Designer removes the selected data column as an output column, sort column, and search condition for the query.
The order in which columns appear in the Grid pane determines their order in the result set. The data column that is topmost in the grid appears leftmost in the result set. The bottom row in the grid contains the data column that appears rightmost in the result set.
If you are creating an Update or Insert query, the order in which you add data columns dictates the order in which the data is processed.
To control where a data column appears or is used, you can move the corresponding row in the Grid pane.
To reorder grid rows
Tip You can also insert a data column into the grid at a specific location by inserting a blank row, and then specifying the data column to insert. For details, see "Adding and Removing Data Columns in the Grid Pane" earlier in this topic.