Add Columns to a Query

Background information

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.

You can add columns individually or as a group. Your choices are:

• An individual column from one table, view, or function, to use for sorting, searching, or summarizing.
• All columns from one table, view, or function. For tables, this is the equivalent of specifying "tablename.*" in the SQL statement.
• All columns from all tables, views, or functions 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, view, or function 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 "*".

Add an individual column

1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
2. In the Diagram pane, select the check box next to the data column that you want to include.

   -or-

   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.

Add all columns for one table, view, or function

1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
2. In the Diagram pane, select the check box next to (All Columns).

   –or–

   Specify objectname.* in the SQL SELECT statement in the SQL pane, substituting the name of your table, view, or function for objectname.

Add all columns for all tables, views, or functions

1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
2. Make sure no join lines in the Diagram pane are selected.
3. Right-click in the query window and choose Properties from the shortcut menu.
4. Select Output all columns.
   –or–
   Specify * in the output list of the SQL SELECT statement in the SQL pane.