ACC2000: How to Create a Total on a Grouped Data Access Page (236791)

MORE INFORMATION

Method 1: Using Promote to Create the Group

1. Open the sample database Northwind.mdb.
2. Create a new data access page in Design view.
3. 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.
4. 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.
5. Right-click the CompanyName field from the Customers table, and then click Promote on the shortcut menu.
6. Add a text box control to the Header:Orders-CompanyName section and set the properties as follows:

      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.
7. 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.

Method 2: Using Group By Table to Create the Group

1. Open the sample database Northwind.mdb.
2. 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.
3. Add the CompanyName field from the Customers table from the field list to the page.
4. 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.
5. 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.)
6. Add a text box control to the Header: Customers section and set the properties as follows:

      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.
7. 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.