MS-Access / Getting Started

---

Tips for Creating a Query

The "Creating a Query in Design View" section (earlier in this tutorial) includes the basics for creating a query in Design view, but you can do so much more. This section delves into a few more aspects of the Creating Queries story.

Adding tables to the query

In order to use a table's fields in a query, you have to display the table name in the top pane of the Design view.

To do that, you need to view all table names by opening the Show Table dialog box. Open the Show Table dialog box, using whichever of the following methods seems most convenient at the moment:

• Right-click the Table pane of Design view and choose the Show Table option from the shortcut menu.
• Click the Show Table button in the Query Setup group of the Design tab on the Ribbon.

After the Show Table dialog box opens, add a table to the query by using whichever of the following methods is most convenient:

• Double-click the table name in the Show Table dialog box.
• Select the table and then click the Add button.

When you add all the tables that you need, click the Close button in the Show Table dialog box to get back to work in Design view.

To remove a table from a query, all you need do is press the Delete key on your keyboard when the table in the Table pane is selected (that is, when any field in the table is highlighted). When a table is deleted from Design view, all the fields in the design grid from that table are deleted too. Because deleting a table from a query is so absurdly easy - and can have damaging consequences for your query - take care when your fingers get close to the Delete key.

If you want to include a field generated by another query, you can add queries to a query by clicking either the Queries tab or the Both tab of the Show Table dialog box, and then double-clicking the query name.

Inserting fields in a design grid

You can move a single field from the Table pane to the design grid in three easy ways:

• Double-click the field name. Access moves the field to the first open column in the grid.
• Drag the field name from the Table pane to the Field row of an unused column in the design grid. This option is popular among dragging fans, or when you want to put a field in a specific location in the grid.
• Use the drop-down list in the Field row of the design grid to choose the field you want. If you use this method with a multiple-table query, you may find choosing the table name from the drop-down Table list before selecting the field name easier. If you don't have the Table row in your design grid, see the "Displaying or hiding table names" section, earlier in this tutorial.

You can place all the field names from one table into the design grid in two ways:

• Put one field name in each column of the grid: If you have criteria for all the fields, you can put one field name in each column of the design grid in just two steps. Double-click the table name in the Table pane of Design view to select all the fields in the table. Then drag the selected names to the design grid. When you release the mouse button, Access puts one name in each column.
• Put all the field names in one column: This method is useful if you want to find something that could be in any field, or if you have one criterion for all the fields in the table. To tell Access to include all field names in one column, drag the asterisk (above the first field name in each table window) to the grid. The asterisk is also available as the first choice in the drop-down Field list in the design grid - it appears as TableName.*.