ACC2000: How to Create a Running Sum on a Data Access Page (233485)

MORE INFORMATION

Running sums display the cumulative total of some value as records are listed on a form or report, or, as described in this article, a DAP. For additional information about running sums in reports and forms, click the article numbers below to view the articles in the Microsoft Knowledge Base: The methods you use for reports and forms are not available with DAPs, but you can use either of the methods described here to accomplish the same result.

Method 1: Base the DAP on a Query That Calculates a Running Sum

Although DSum() cannot be used in a DAP, it can be used in a query that provides data for the page, which will allow the page to display the required information. The following example will show you how to do this using the Northwind sample database.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

To create a query that calculates a running sum, follow these steps:

1. Start Access and open the sample database Northwind.mdb.
2. Create a new query in Design view, add the Orders table, and click Close.
3. On the View menu, click Totals.
4. In the first column of the query grid, add the following:

      Field: EmpAlias:[EmployeeID]
      Total: Group By
   					

5. In the second column, add the following:

      Field: [Freight]
      Total: Sum
   					

6. In the third column, add the following:

      Field: RunSum:Format(DSum("[Freight]","Orders","[EmployeeID]<="&[EmpAlias]),"Currency")
      Total: Expression
   					

7. Save the query as qry Freight RunSum. Run the query.
   
   The RunSum field displays the cumulative sum of the Freight totals.

For additional information about calculating running sums in queries, click the article number below to view the article in the Microsoft Knowledge Base: This query can now be used as the basis for a DAP that will display the running totals. To do that, follow these steps:

1. Under Objects, click Pages, and then click New.
2. In the Choose the table ... list, select qry Freight RunSum, and then click OK to create a new page based on that query.
3. Drag all the qry Freight RunSum fields from the field list onto the Header area of the page and arrange them in a horizontal line.
4. Delete the Navigation section of the page and allow the DataPageSize property to be set to All so that all records will be displayed on the same page, including the running sum.
5. Shorten the Header section of the page and arrange the controls so that you can see the data conveniently.
6. Save the page as Freight DSum. If you receive a message about the connection string path, click OK.
7. Switch to Page view and confirm that the running sum is correctly displayed.

Method 2: Use a Script to Calculate the Running Sum on the DAP

Using DSum() in a query has the disadvantage that the function is executed for each row of the query, which may slow response with larger recordsets. In those cases, it may be preferable to use a script written in the Microsoft Visual Basic scripting language to calculate the running sum on the DAP.

To use a script instead of DSum() in the query as above, follow these steps:

1. Open qry Freight RunSum in Design view, delete the field RunSum, and then save the query as qry Freight by Employee.
2. Create a new DAP as described above, using both fields from qry Freight by Employee, and saving the page as Freight Script.
3. Create an unbound text box on the Header area of the page; it will have the name Text0 by default.
4. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
5. In the Script Outline window, double-click MSODSC, and then double-click DataPageComplete.
6. IMPORTANT: When you create VBScript blocks for MSODSC events, you must add a parameter to the event name as follows:

   <SCRIPT LANGUAGE=vbscript FOR=MSODSC EVENT=Current(oEventInfo)>

   The <I>oEventInfo</I> parameter returns specific information about the event to the script. You must add this parameter, whether or not it will be used, because the script will not work without it.
   In this case, the event is DataPageComplete, so the opening SCRIPT tag should be this:

   <SCRIPT language=vbscript for=MSODC event=DataPageComplete(oEventInfo)>
   						

   Insert the following script:

   <SCRIPT event=DataPageComplete(oEventInfo) for=MSODSC language=vbscript>
    <!--
     Dim i
   
     For i = 0 To SumOfFreight.length - 1
         If i = 0 Then
             Text0(i).value = CSng(SumOfFreight(i).value)
         Else
             Text0(i).value = CSng(SumOfFreight(i).value) + CSng(Text0(i-1).value)
         End If
         Text0(i).value = FormatCurrency(Text0(i).value)
     Next
    -->
   </SCRIPT>
   						

7. Save the script and close the Script Editor.
8. Switch to Page view and confirm that the running sum is correctly displayed.

For more information about using scripts in Web pages, click Microsoft Access Help on the Help menu, type vbscript in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.