Before you use the query designer to create a query, it might help to have some general guidelines about the designer and how to work with it.
The query designer window is divided into two parts. At the top is the field list area. You can insert field lists for the tables and queries that you want to query. When you open the designer, the Add Tables dialogue will appear. The tables and queries you choice will be displayed in the field list area. You can also add tables and queries to the field list area by dragging them from the Database Window (Access 2003 and earlier) or the Navigation Pane (Access 2007, 2010).
Below the Field List Area is the Grid. Each column in the grid represents a single field in the query results. The first cell in each column indicates the field or calculation you want to appear in the query result. You can add a field to this cell by
- dragging it from a field list,
- double clicking the field name,
- clicking the field dropdown, or
- typing the name of the field in the cell
Double-clicking a field name in a field list will put the field in the first available column in the grid. Dragging and dropping a field lets you choose where you want in the grid. While it is possible to type a field name, the other methods are usually preferred if only to avoid the nuisance of mistyping a field name and having to enter the relevant table name (when the query will draw on more than one table or query for its data) in the next cell.
The second cell is for the name of the table or query to which the field in the first cell belongs. There is an option to hide this row but unless the query involves only a single table or query as its data source, it is usually preferable to leave this row visible. Unless you type the field name in the first cell, Access will automatically fill in this value.
The third cell controls the sort order for the query field. The choices are
- none (default)
When setting up sorting, the order of the fields in the grid from left to right is important. Priority is given to the leftmost sorted fields. The next sorted field to the right acts as a tie breaker when there are several identical values in the first sorted field. It is possible to have several secondary sort field. Each secondary sort field acts as a tie-breaker for identical values in the sort field to its left.
Below the sort cell is the Show cell. Be default the checkbox is check meaning that the field will display in the query’s result. If the box is unchecked then that query field will not appear in the query results.
Below the Show row are the criteria rows. This is where you specify conditions that will determine whether a record should be included in or excluded from the query results. Specified criteria in interact with criteria in other columns according to set logical rules.
Conditions on the same criteria row must both be true for a record to be displayed. If conditions on different rows, then a record will be displayed if one or more of the conditions is true.
The tutorials in this series will give you lots of practice in using the Query Design Window
Next: Creating a Single Table Select Query