Unit 7. Queries (II)

The Design view.

If we look at the screen above, we have a tables area, in this area we put the tables that contain the data we need or we want to see in the result of the query, and in the part below named the QBE grid we define the query.

Every column in the QBE grid corresponds to a field.

Every row has a function:

Field: here we place the field to use which will usually be the field to visualize, it could be a field from the table or a calculated field.

Table: name of the table we want to extract the field from. This will be usefull when we are defining queries based on various tables.

Sort: serves to arrange the resulting rows in a determinated order.

Show: if not marked, the column does not appear in the result, it is usually unmarked when we want to use the field to define the query but don't want the field to appear in the result.

E.g if we want the query to extract all the students from Denver we need the City field to select the students, but we do not want the city to appear in the result as we know that they are all from the same city that is Denver.

Criteria: serve to specify the lookup criteria. A lookup criteria is a condition that records need to comply with to appear in the result of the query. It can be made up from one or from various conditions, joined by AND and OR operators.

or: this row and those after are used to make multiple-conditions.

Adding fields

To add fields to the QBE grid we can:

Double click on the name of the field appearing in the tables area, this will be placed in the first vacant column of the QBE grid.

click on the name of the field, and drag it to the grid, over the column in front of which we want to leave the field that we are adding.

Click on the Field: row of an empty column in the grid, an arrow will appear to the right with a drop down list with all the fields from all the tables that appear in the tables area. If we have many fields and various tables we can reduce the list by first selecting a table from the Table: row, this way only fields from this selected table will appear.

We can also type the name of the field directly in the Field: row of an empty column in the grid.

If we want all the fields from the table to appear in the result of the query we can use the asterisk * (synonymous with 'all the fields'). Selecting the asterisk has an advantage over selecting all the fields: When you use the asterisk, the query results automatically include any fields that are added to the underlying table after the query is created, and automatically exclude fields that are deleted.

Defining calculated fields

Calculated fields are obtained from the result of an expression.

If you would like to know more about how to form expressions, clic here .

Column headings

The column heading usually contains the name of the column, but we can change the headings of columns in order to make the result of the query clearer.

If we want to change this heading we must indicate this in the Field: row, writing it in front of the name of the field and followed by a colon (:). These are usually used for calculated fields. In the query that appears above you have an example in the second column, this one will have Year in the heading, and in the column we will see the year of the date of birth (we have a calculated field that uses the year( ) function, the function that obtains the year of a date).

Changing the order of the fields

If we want to change the order of fields that have been included in the grid we can move a column (or various) either by dragging it or by cutting and pasting it.

To move a column by dragging it:

Position the cursor over the extreme top part of the column and when the arrow appears clic, and the column will appear standing out (it is selected).

Move the cursor slightly so that the arrow appears. Press the mouse button and mantaining it down, drag the column to the desired position.

To move a column by cutting it: Select the column (position the cursor over the extreme top part of the column and when the arrow appears clic).

Click on the button (or select the Cut option from the Edit menu, or, press Ctrl+X), the column will disappear.

Next create a column in blank in the position where we want to move the column that we have cut with the Columns option from the Insert menu.

Select this column and click on the button (or select the Paste option from the Edit menu, or, press Ctrl+V).

We can select various consecutive columns by selecting the first and maintaining the Shift key depressed, selecting the last column to select, both these columns and any found between them will be selected.

Page. 7.2