Shape Compute clause

Applies to: Access 2013, Office 2013

A shape COMPUTE clause generates a parent Recordset, whose columns consist of a reference to the child Recordset; optional columns whose contents are chapter, new, or calculated columns, or the result of executing aggregate functions on the child Recordset or a previously shaped Recordset; and any columns from the child Recordset listed in the optional BY clause.

Syntax

 
SHAPE child-command [AS] child-alias 
   COMPUTE child-alias [[AS] name], [appended-column-list] 
   [BY grp-field-list] 

Description

The parts of this clause are as follows:

  • child-command

    • Consists of one of the following:

      • A query command within curly braces ("{}") that returns a child Recordset object. The command is issued to the underlying data provider, and its syntax depends on the requirements of that provider. This will typically be the SQL language, although ADO does not require any particular query language.

      • The name of an existing shaped Recordset.

      • Another shape command.

      • The TABLE keyword, followed by the name of a table in the data provider.

  • child-alias

    • An alias used to refer to the Recordset returned by the child-command. The child-alias is required in the list of columns in the COMPUTE clause and defines the relation between the parent and child Recordset objects.
  • appended-column-list

    • A list in which each element defines a column in the generated parent. Each element contains either a chapter column, a new column, a calculated column, or a value resulting from an aggregate function on the child Recordset.
  • grp-field-list

    • A list of columns in the parent and child Recordset objects that specifies how rows should be grouped in the child. For each column in the grp-field-list, there is a corresponding column in the child and parent Recordset objects. For each row in the parent Recordset, the grp-field-list columns have unique values, and the child Recordset referenced by the parent row consists solely of child rows whose grp-field-list columns have the same values as the parent row.

If the BY clause is included, the child Recordset's rows will be grouped based on the columns in the COMPUTE clause. The parent Recordset will contain one row for each group of rows in the child Recordset.

If the BY clause is omitted, the entire child Recordset is treated as a single group and the parent Recordset will contain exactly one row. That row will reference the entire child Recordset. Omitting the BY clause allows you to compute "grand total" aggregates over the entire child Recordset.

For example:

    SHAPE {select * from Orders} AS orders
       COMPUTE orders, SUM(orders.OrderAmount) as TotalSales

Regardless of which way the parent Recordset is formed (using COMPUTE or using APPEND), it will contain a chapter column that is used to relate it to a child Recordset. If you wish, the parent Recordset may also contain columns that contain aggregates (SUM, MIN, MAX, and so on) over the child rows. Both the parent and the child Recordset may contain columns that contain an expression on the row in the Recordset, as well as columns that are new and initially empty.

Operation

The child-command is issued to the provider, which returns a child Recordset.

The COMPUTE clause specifies the columns of the parent Recordset, which may be a reference to the child Recordset, one or more aggregates, a calculated expression, or new columns. If there is a BY clause, the columns it defines are also appended to the parent Recordset. The BY clause specifies how the rows of the child Recordset are grouped.

For example, assume you have a table — Demographics — consisting of State, City, and Population fields (the population figures are solely for illustration).

State

City

Population

WA

Seattle

700,000

OR

Medford

200,000

OR

Portland

400,000

CA

Los Angeles

800,000

CA

San Diego

600,000

WA

Tacoma

500,000

OR

Corvallis

300,000

Now, issue this shape command:

 
rst.Open  "SHAPE {select * from demographics} AS rs "  & _ 
          "COMPUTE rs, SUM(rs.population) BY state", _ 
           objConnection 

This command opens a shaped Recordset with two levels. The parent level is a generated Recordset with an aggregate column (SUM(rs.population) ), a column referencing the child Recordset (rs ), and a column for grouping the child Recordset (state ). The child level is the Recordset returned by the query command (), a column referencing the child Recordset (rs ), and a column for grouping the child Recordset (state ). The child level is the Recordset returned by the query command (select * from demographics ).

The child Recordset detail rows will be grouped by state, but otherwise in no particular order. That is, the groups will not be in alphabetical or numerical order. If you want the parent Recordset to be ordered, you can use the Recordset Sort method to order the parent Recordset.

You can now navigate the opened parent Recordset and access the child detail Recordset objects. For more information, see Accessing Rows in a Hierarchical Recordset.

Resultant Parent and Child Detail Recordsets

Parent

SUM (rs.Population)

rs

State

1,300,000

Reference to child1

CA

1,200,000

Reference to child2

WA

1,100,000

Reference to child3

OR

Child1

State

City

Population

CA

Los Angeles

800,000

CA

San Diego

600,000

Child2

State

City

Population

WA

Seattle

700,000

WA

Tacoma

500,000

Child3

State

City

Population

OR

Medford

200,000

OR

Portland

400,000

OR

Corvallis

300,000