To use a column in a query, you must add it to the query. You might add a column to display it in query output, to use it for sorting, to search the contents of the column, or to summarize its contents.
If you are creating a Select query and add a column in the Diagram or Grid panes, the column you add becomes part of the query output. You can remove the column from the output and still use it for sorting, searching, and so on. For example, to find all employees in the accounting department, you might search the department column but not display it in the output.
Tip Wherever you use a column in a query, you can also use an expression that can consist of any combination of columns, literals, operators, and functions. For details, see Using Expressions in Queries.
You can add columns individually or as a group. Your choices are:
- An individual column from one table or table-structured object, to use for sorting, searching, or summarizing.
- All columns from one table or table-structured object. For tables, this is the equivalent of specifying "tablename.*" in the SQL statement.
- All columns from all table-structured objects in the query, which can be useful if you are working with joins. This option is the equivalent of specifying "*" alone in the SQL statement when more than one table or table-structured object is used in the query.
When you add all columns, the Query Designer does not add all individual columns to the query; it instead uses the asterisk ("*" ). If you need to work with a specific column, you must add it separately.
Note When you choose "*", all currently defined columns for the tables you are using are included. If a table definition changes, the list of columns returned by "*" changes as well. It is recommended that, if practical, you specify the columns you want to work with instead of using "*".
To add an individual column
- In the Diagram pane, select the check box next to the data column that you want to include.
- In the Grid pane, move to the first blank grid row where you want to add the column, click the field in the Column column, and select a column name from the list.
Note To add a data row at a specific location in the Grid pane, select the grid row where you want to add the new column and press INS. A new column is added above that row.
To add all columns for one table or table-structured object
In the Diagram pane, select the check box next to (All Columns).
- Specify objectname.* in the SQL statement in the SQL pane, substituting the name of your table or table-structured object for objectname.
To add all columns for all tables and table-structured objects
- Make sure no join lines in the Diagram pane are selected.
- Right-click in the query window and choose Properties from the shortcut menu. Then choose the Query tab.
- Select Output all columns.
Specify * in the output list of the SQL statement in the SQL pane.