Creating a Query

After you create your tables and place data in them, you’re ready to work with queries. To begin a query, choose the Create tab of the Ribbon, and click on the Query Design button in the Other group. Access opens the query designer in response.

Figure 36-4 shows two windows. The underlying window is the Query Designer. Floating on top of the designer is the Show Table dialog box. The Show Table window is modal, which means that you must do something in the dialog box before continuing with the query. Before you continue, you add the tables required for the query. In this case, tblProducts is highlighted to be added.

The Show Table dialog box shown in Figure 36-5 displays all tables and queries in your database. Double-click on tblProducts to add it to the query design. Close the Show Table dialog box after adding tblProducts. Figure 36-5 shows tblProducts added to the query.

To add additional tables to the query, right-click on the query’s design surface and select Show Table from the shortcut menu that appears. Alternatively, drag tables from the Navigation pane on to the Query Designer’s surface.

Removing a table from the Query Designer is easy. Just right-click on the table in the Query Designer and select Remove Table from the shortcut menu.

Using the Query window

The Query window has two main views: Design view and Datasheet view. The difference between them is self-explanatory: The Design view is where you create the query, and the Datasheet view displays the records returned by the query.

The Query Design window should now look like Figure 36-5, with tblProducts displayed in the top half of the Query Design window.

The Query Design window consists of two sections:

• The table/query entry pane (top)

• The Query by Example (QBE) design grid (bottom)

The upper pane is where tables or queries and their fields are displayed. Tables and queries are displayed as small windows inside the top pane (the proper name of this window is Field List). The Field List window can be resized by clicking on the edges and dragging it to a different size.

The Query by Example (QBE) grid holds the field names involved in the query and any criteria used to select records. Each column in the QBE grid contains information about a single field from a table or query contained within the upper pane.

Navigating the Query Design window

The two window panes are separated horizontally by a pane-resizing bar (see Figure 36-5). Move the bar up or down to change the relative sizes of the upper and lower panes.

Switch between the upper and lower panes by clicking the desired pane or by pressing F6 to switch panes. Each pane has horizontal and vertical scrollbars to help you move around.

You actually build the query by dragging fields from the upper pane to the QBE grid.

Using the Design tab in Query Design view

The Design tab that appears on the Ribbon (shown in Figure 36-6) when you’ve displayed the Query Design view contains many different buttons specific to building and working with queries.

This Design tab has many buttons that can be helpful when designing your queries. Although each button is explained as it is used in these chapters about Access, here are the main buttons:

• View: Switches between the Datasheet view and Design view. The View drop-down control also enables you to display the underlying SQL statement behind the query (more on this later).

• Save (in the Quick Access Toolbar): Saves the query. It is a good idea to save your work often, especially when creating complex queries.

• Make Table, Append, Update, and Crosstab: Specify the type of query you are building.

• Run: Runs the query. Displays a select query’s datasheet, serving the same function as the View button. However, when working with action queries, it actually performs the operations specified by the query.

• Show Table: Opens the Show Table dialog box.

The remaining buttons are used for creating more advanced queries, printing the contents of the query, and displaying a query’s property sheet.

Using the QBE grid of the Query Design window

As you saw earlier, Figure 36-5 displays an empty QBE grid, which has six labeled rows:

• Field: Where field names are entered or added

• Table: Shows the table the field is from (useful in queries with multiple tables)

• Sort: Enables sorting instructions for the query

• Show: Determines whether to display the field in the returned recordset

• Criteria: Criteria that filter the returned records

• or: This row is the first of a number of rows to which you can add multiple query criteria.

You learn more about these rows as you create queries in this chapter.