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

• Microsoft Access 2000

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

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.

REFERENCES

For more information about adding a footer to a data access page, click Microsoft Access Help on the Help menu, type data access page footer in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: inf

Keywords          : kbdta AccDAP DAPHowTo 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 28, 1999