Microsoft KB Archive/232595

= ACC2000: How to Sum a Calculation in a Data Access Page =

Article ID: 232595

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q232595



Moderate: Requires basic macro, coding, and interoperability skills.

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



SUMMARY
This article shows you how to sum calculations in a Microsoft Access data access page.



MORE INFORMATION
The following example creates a data access page that displays a product (in the form of a number), the price associated with each product, and how many of each product was ordered.

Because the page will contain the cost per product and how many of each were ordered, the first calculation on the page will multiply the number of products ordered times the cost per product. This is called the Extended Price.

The page will display the various products associated with each order. The second calculation will therefore calculate the total revenue per order. This is called the Order Total.

The last calculation will be the sum all of the orders combined to come up with a grand total of revenue. This is called the Grand Total.

Steps for Creating the Data Access Page

 * 1) Open the sample Access database file Northwind.mdb or the sample Access project file NorthwindCS.adp.
 * 2) In the Database window, click Pages under Objects, and then double-click Create data access page by using wizard.
 * 3) From the Tables/Queries (or Tables/Views) list, select Table: Order Details, and then click the >> button to move all items from the Available Fields list to the Selected Fields list. Click Next.
 * 4) If the wizard does not automatically group by OrderID, click OrderID in the list, and then click the > button to group the page by OrderID. Click Finish.
 * 5) On the View menu, click Sorting and Grouping, and for the Order Details-OrderID group, make sure that both Group Header and Group Footer are set to Yes.
 * 6) Set the Height property of the Header: Order Details section to 1.25in.
 * 7) Save the page as dapSumGroups and view the page, noting the current layout.

Steps for Summing the Extended Price
 Add a new unbound text box, aligned directly under the Discount text box.  Set the following properties for this new text box:   ID: txtExtendedPrice ControlSource: XPrice: [UnitPrice]*[Quantity] Left: 2.333in ReadOnly: True NOTE: This calculation does not make adjustments for any discount.

  For the label that is associated with this new text box, set the following properties:   InnerText: Extended Price Left: 1.25in  Save and view the page. Note how the page is now calculating total revenue per product.

Steps for Summing Total Revenue Per Order
 Add a new text box to the Footer: Order Details-OrderID section.</li>  Set the following properties for this new text box: <pre class="fixed_text">  ID: txtOrderTotal ControlSource: GroupOfXPrice: XPrice TotalType: Sum ReadOnly: True </li>  For the label that is associated with this new text box, set the following properties: <pre class="fixed_text">  InnerText: Order Total </li> Save and view the page. Note how the page now displays the total revenue per order.</li></ol>

Steps for Summing the Grand Total of All Orders
 Add a new text box to the Header: Order Details-OrderID section.</li>  Set the following properties for this new text box: <pre class="fixed_text">  ID: txtGrandTotal ControlSource: GroupOfXPrice: XPrice TotalType: Sum ReadOnly: True </li>  For the label that is associated with this new text box, set the following properties: <pre class="fixed_text">  InnerText: Grand Total </li> Select the txtGrandTotal text box, and click the Promote button on the Page Design toolbar.</li> Select the Expand control to the left of the txtGrandTotal text box and press the DELETE key.</li> Select the Navigation Bar in the NavigationSection: Order Details-XPrice section and press the DELETE key.</li> Save and view the page. Note that now, in addition to the other totals, the grand total revenue of all orders is displayed.</li></ol>

<div class="references_section">