Microsoft KB Archive/236791

= ACC2000: How to Create a Total on a Grouped Data Access Page =

Article ID: 236791

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q236791



Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
Grouped data access pages show data in a hierarchy, much like grouped reports. Parent data is displayed in the group header, and the child data is displayed in a separate section of the page. This type of page often displays the total count of records, the sum of a value in the child records, or the average value of child records within the parent section. You can create a grouped data access page in two ways, depending on how you initially group the page.



MORE INFORMATION
The following examples use the sample database Northwind.mdb to illustrate the scenario described in the "Summary" section. The Customers table represents the parent data, and the Orders table represents the child data. This example displays the total amount of freight from the Orders table for each customer listed on the page.

Method 1: Using Promote to Create the Group
 Open the sample database Northwind.mdb. Create a new data access page in Design view. If the field list is not displayed, click Field List on the View menu, and then add the CompanyName field from the Customers table to the page. If they are not expanded, expand Related Tables under the Customers table by clicking the plus sign (+), expand the Orders table under Related Tables by clicking the plus sign, and then add the OrderID, OrderDate, and Freight fields from the Orders table to the page. Right-click the CompanyName field from the Customers table, and then click Promote on the shortcut menu.  Add a text box control to the Header:Orders-CompanyName section and set the properties as follows: <pre class="fixed_text">  Text Box ---  Control Source: Freight Total Type: Sum

Label -  Inner Text: Total Freight NOTE: You can also add this text box to the footer section of the page. </li> Save the page as dapGroupByPromoteExample and view the page in Page view. Note that the total of the freight per customer is shown next to the company name. To see the detail of each record, click the plus sign (+) next to the company name.</li></ol>

Method 2: Using Group By Table to Create the Group
<ol> Open the sample database Northwind.mdb.</li> Create a new data access page in Design view.

NOTE: If you use the Page Wizard in this step, the Group by Table option is not available, so be sure to add the fields manually from the field list.</li> Add the CompanyName field from the Customers table from the field list to the page.</li> If they are not expanded, expand Related Tables under the Customers table by clicking the plus sign (+), expand the Orders table under Related Tables by clicking the plus sign, and then add the OrderID, OrderDate, and Freight fields from the Orders table to the page.</li> Click the CompanyName field, and then on the Page Design toolbar, click Group by Table. (The Group by Table button looks like a large, left-facing arrow with a small table below it.)</li>  Add a text box control to the Header: Customers section and set the properties as follows: <pre class="fixed_text">  Text Box -  Control Source: Freight (Type the value "Freight" (without the quotation marks) into the property box.) Total Type: Sum

Label -  Inner Text: Total Freight NOTE: You can also add this text box to the footer section of the page. </li> Save the page as dapGroupByTableExample and view the page in Page view. Note that the total of the freight per customer is shown in the Customers footer section.</li></ol>

<div class="references_section">