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

ID: Q232595

The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and 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

  1. Add a new unbound text box, aligned directly under the Discount text box.


  2. Set the following properties for this new text box:


    3. 
   ID: txtExtendedPrice
   ControlSource: XPrice: [UnitPrice]*[Quantity]
   Left: 2.333in
   ReadOnly: True
    NOTE: This calculation does not make adjustments for any discount.

  3. For the label that is associated with this new text box, set the following properties:


    4. 
   InnerText: Extended Price
   Left: 1.25in
  4. Save and view the page. Note how the page is now calculating total revenue per product.


Steps for Summing Total Revenue Per Order

  1. Add a new text box to the Footer: Order Details-OrderID section.


  2. Set the following properties for this new text box:


    3. 
   ID: txtOrderTotal
   ControlSource: GroupOfXPrice: XPrice
   TotalType: Sum
   ReadOnly: True
  3. For the label that is associated with this new text box, set the following properties:


    4. 
   InnerText: Order Total
  4. Save and view the page. Note how the page now displays the total revenue per order.


Steps for Summing the Grand Total of All Orders

  1. Add a new text box to the Header: Order Details-OrderID section.


  2. Set the following properties for this new text box:


    3. 
   ID: txtGrandTotal
   ControlSource: GroupOfXPrice: XPrice
   TotalType: Sum
   ReadOnly: True
  3. For the label that is associated with this new text box, set the following properties:


    4. 
   InnerText: Grand Total
  4. Select the txtGrandTotal text box, and click the Promote button on the Page Design toolbar.


  5. Select the Expand control to the left of the txtGrandTotal text box and press the DELETE key.


  6. Select the Navigation Bar in the NavigationSection: Order Details-XPrice section and press the DELETE key.


  7. Save and view the page. Note that now, in addition to the other totals, the grand total revenue of all orders is displayed.



REFERENCES

For more information about sums in data access pages, click Microsoft Access Help on the Help menu, type "Calculate a grand total or other aggregate for all records on a data access page" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: 


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

Last Reviewed: July 8, 1999