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

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.

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:
   • Customers: CustomerID
   • Orders: OrderID
   • Orders: Freight
5. Click Next.
6. On the second page of the Page Wizard, choose to group the page by CustomerID, and then click Finish.
7. In Design view of the data access page, select the Freight field, and then move the Freight field to the Header: Orders-CustomerID section.
8. Change the TotalType property of the Freight field to Sum.
9. 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.

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:
   • Customers: CustomerID
   • Orders: OrderID
   • Orders: Freight
5. Click Next.
6. On the second page of the Page Wizard, choose to group the page by CustomerID, and then click Finish.
7. Add a new text box to the CustomersWiz section of the page, and then set the ControlSource property to =1.
8. With the text box still selected, move the text box to the Header: Orders-CustomerID section.
9. Change the TotalType property of the text box to Count.
10. On the View menu, click Page View. Note that the page displays the number of orders per customer in the Orders table.