Unit 7. Queries (IV)

Ordering rows

To sort the rows in the result of the query we use the Sort: row in QBE grid.

Click in the Sort: row on the field, in which you wish to arrange the rows, then select from the drop down list if you want the rows appear in ascending or descending order.

If you choose Ascending, rows will be arranged from less to more if the field is numeric, in alphabetical order if the field is Text type, from earlier to later if the field is datetime type; with Descending rows will be arranged in reverse order.

We can also perform complex sorts . This means we can sort records by several fields and sort in ascending order by some fields and in descending order by others.

When we assign order to severals columns (fields) the rows will be sorted by the first ordered column, for the same value in this column, they are sorted by the second ordered column, and successively.That is, if we want to sort by State and within the same State by City, we need firstly the State column and then the City column in the QBE Grid, and each of them with the ordering mode we want for it. The ordering mode is independant and so a different ordenation can be used for each column. E.g ascending for the State column, and then descending for the City column.

Selecting rows

When we recover data from a table we can recover all the rows or only certains.

To limit the result to several rows we must specify a criteria (a condition) that limits the results to records that match this criteria. Criteria: row serves to that purpose.

For example, we want to see the students that live in "Denver", so the rows we want must match the condition City = "Denver" . So we will add a criteria to the QBE grid in City field typing the rest of the condition (="Denver") in Criteria: row.

When a condition is an equality it is not necessary to type = sign, we can type the Denver value directly in the Criteria: row, as if we do not put an operator Access assumes an = by default.

In the Criteria: row we can type a field name (to compare two fields with each other) in this case we need to enclose the field name in brackets []. E.g we want to make the condition cost = price in which price and cost are two fields, in Cost column we will type [price] in the criteria row.

We can combine several conditions with AND and OR operators.

In a search criteria in which the conditions are combined with AND operator, records must to comply with all the conditions to appear in the result. E.g price > 100 and price < 1200, all the records with a price of between 101 and 1199 will appear.

In a search criteria in which the conditions are combined with OR operator, a record will appear in the result if it complies with at least one of the conditions.

All the conditions established in the same row of the grid are combined with an AND operator.

In the next example the criteria built is aulaclic_state = "CA" AND aulaclic_date between #01/01/2000# and #07/14/2005#

When we type criteria in the same column but in more than one Criteria cell (using the 0: rows and those following), Microsoft Access combines them using the OR operator.

E.g, if we have the following criteria specified in the QBE grid:

We will visualize the Name, State and Date of the Table1 table records, but only those (from CA and date between 01/01/2000 and 07/14/2005), or those from NY with any date.

Be Advised! The date criteria only combine the the CA state criteria because it is located in the same row.

If you want to know more about the condition operators, click here .

To practice these operations you can perform the Step by step exercise in creating queries.

Page. 7.4