Microsoft KB Archive/203107

= HOW TO: Use the TotalType Property of a Control on a Data Access Page in Access 2000 =

Article ID: 203107

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q203107



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

IN THIS TASK
SUMMARY
 * Scenario 1
 * Scenario 2



SUMMARY
The TotalType property of a control on a data access page allows you to work with the value in either a bound or a calculated control by applying the following functions:
 * None
 * Sum
 * Avg
 * Min
 * Max
 * Count
 * Any
 * Stdev

The TotalType property allows the calculation or nested calculation of a value within the same control that it is created from. In other words, the calculation is performed on the same valueset on which the control is based.

When a calculated control on a data access page is promoted, the TotalType property of that control is changed from its current setting, which by default is None, to Count. However, this property remains editable, and can be set to any valid values, even after it is promoted.

back to the top

Scenario 1
One common scenario where you might use the TotalType property on a grouped data access page is when you want to total one of the fields in the child records and to display that total in the parent record. Using the Northwind.mdb database as an example, you can display the total amount of freight from each order on a customer level as follows:
 * 1) Open the sample database Northwind.mdb.
 * 2) In the database window, click Pages under Objects, and then click New.
 * 3) In the New Data Access Page dialog box, click Page Wizard, and then click OK.
 * 4) On the first page of the Page Wizard, add the following fields to the page:
 * 5) * Customers: CustomerID
 * 6) * Orders: OrderID
 * 7) * Orders: Freight
 * 8) Click Next.
 * 9) On the second page of the Page Wizard, choose to group the page by CustomerID, and then click Finish.
 * 10) In Design view of the data access page, select the Freight field, and then move the Freight field to the Header: Orders-CustomerID section.
 * 11) Change the TotalType property of the Freight field to Sum.
 * 12) On the View menu, click Page View. Note that the Freight field is now a calculated field that totals the amount of freight from each order for every customer.

back to the top

Scenario 2
Another common scenario where you might use the TotalType property on a grouped data access page is to count the number of child records and to display that total in the parent record. Using the Northwind.mdb database as an example, you can display the total number of orders for each customer as follows:
 * 1) Open the sample database Northwind.mdb.
 * 2) In the database window, click Pages under Objects, and then click New.
 * 3) In the New Data Access Page dialog box, click Page Wizard, and then click OK.
 * 4) On the first page of the Page Wizard, add the following fields to the page:
 * 5) * Customers: CustomerID
 * 6) * Orders: OrderID
 * 7) * Orders: Freight
 * 8) Click Next.
 * 9) On the second page of the Page Wizard, choose to group the page by CustomerID, and then click Finish.
 * 10) Add a new text box to the CustomersWiz section of the page, and then set the ControlSource property to =1.
 * 11) With the text box still selected, move the text box to the Header: Orders-CustomerID section.
 * 12) Change the TotalType property of the text box to Count.
 * 13) On the View menu, click Page View. Note that the page displays the number of orders per customer in the Orders table.

back to the top

Additional query words: inf stdev sum avg min max count

Keywords: kbhowto kbhowtomaster kbdap KB203107

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.