Condividi tramite

Organizing Query Results

  • Articolo

With the output of your query defined, you can now organize the records that appear in your results by ordering and grouping on the output fields. If you want, you can also filter the groups that appear in your results.

Ordering Query Results

Ordering determines the order in which records or rows will be sorted when they appear in the output of your query. For example, you could sort records by State and then by City, or sort them in ascending order based on the outstanding account balance.

Use the Order By tab to set the sort order for your query. The sort order determines the order in which rows or records will appear in your query output.

First select the fields you want to use from the Selected Fields box and move them to the Ordering Criteria box. Then arrange the fields in the order you want them to appear in the query results.

To set ordering criteria

  1. In the Selected fields box, select a field name.
  2. Choose Add.

To remove ordering criteria

  1. Select one or more fields that you want to remove.
  2. Choose Remove.

The order in which fields appear in the Ordering criteria box determines the order of importance when the query results are sorted. The first field determines the primary sort order.

For example, if the first field in the Ordering criteria box is Customer.region and the second field in the Ordering criteria box is Customer.city, the results will be ordered by Customer.region. If more than one record in the customer table has the same region field value, these records are additionally ordered by Customer.city.

To adjust the importance of a sort order field, use the button to the left of the field to drag the field to the desired location in the Ordering criteria box.

You can sort in ascending or descending order by setting the buttons in the Order options area. Each order field appears in the Output fields box on the Filter tab with an up arrow or down arrow next to it, indicating whether the sort on that field is to be made in ascending or descending order.

Grouping Query Results

Grouping consolidates or gathers similar records into one record so you can perform calculations based on groups of records. For example, you might want to find the sum of all orders to a specific region. Instead of looking at all the records individually, you can group all the records from the same region into one record, and get the sum of all the orders from that region. To control how records are grouped, use the Group By tab in the Query Designer.

Grouping is most useful when used in conjunction with an aggregate function, such as SUM, COUNT, AVG, and so on.

For example, suppose you want to see the total dollar amount of orders for each customer ID number in your orders table. You need to group all the order records for a given customer ID into one record, and find the sum of the order amounts.

You first use the Fields tab to add the expression SUM(Orders.order_net) to the output of your query, then use the Group By tab to group the results by customer ID number. The results show total net orders for each customer:

To set grouping options

  1. In the Fields tab, type the expression in the Functions and expressions box.

    -or-

    Choose the dialog button to use the Expression Builder to enter an expression in the Functions and expressions box.

  2. Choose the Add button to place the expression in the Selected fields box.

  3. In the Group By tab, add the expression to group results by.

You can also set a filter on the grouped results.

Selecting the Groups You Want

To set a filter on grouped or consolidated records rather than on individual records, choose Having on the Group By tab. You can use a field name, an aggregate function on a field name, or another expression in the Field Name box.

Building on the previous example, you could use the query that shows total sales by customer number, then use the Having button to restrict the output to customers having more than $50,000 in net orders:

To set a Having option on a group

  1. In the Group By tab, choose Having.
  2. In the Having dialog box, select a function and field name in the Field Name field.
  3. Choose OK.

See Also

Working with Queries | Defining Query Results | Running Queries | Customizing Queries | Order By Tab, Query and View Designers | Querying Multiple Tables and Views | Updating Data in a View | Creating Queries