To create running totals using a formula

1. Create a report using the sample data, Xtreme.mdb located in the File Data Sources folder of the Data Explorer dialog box. Link the Customers and Orders tables and place the following fields from left to right in the Details section:

{customer.CUSTOMER NAME}
{orders.ORDER ID}
{orders.ORDER AMOUNT}

2. On the Insert menu, click Formula Field then double-click Formula Fields in the Field Explorer dialog box.
3. Name the formula @RunningTotal and click OK.
4. Enter the following into the Formula Editor:

WhilePrintingRecords;
CurrencyVar Amount;
Amount := Amount + {orders.ORDER AMOUNT};

5. Click Save and Close.
6. Place this formula in the Details section of your report, just to the right of the {orders.ORDER AMOUNT} field.

   This formula prints the running total of the values in the Order Amount field.

7. On the Insert menu, click Group and group the report on the {customer.CUSTOMER NAME} field.
8. In the Formula Editor, create @AmountReset:

WhilePrintingRecords;
CurrencyVar Amount := 0;
This formula says:
Set the value in the Amount variable to 0.

9. Place this formula in the Group Header #1 section of your report.

   Because the Group Header #1 section appears once for every group, @AmountReset will execute each time the group changes. Thus, the Amount variable is reset to 0 each time a new group begins.

10. Select the @AmountReset formula on the report and use the Format Editor to suppress it so that it will not appear in the final print-out.
11. In the Formula Editor, create @AmountDisplay:

WhilePrintingRecords;
CurrencyVar Amount;
This formula simply displays the current value of the Amount variable at any time.

12. Place this formula in the Group Footer #1 section of your report.

    Because the Group Footer #1 section appears once for every group, @AmountDisplay will execute each time a group ends. Thus, the value stored in the Amount variable will be printed each time the group changes.

    

Note:    This formula prints the same value that @RunningTotal prints as the running total for the last record in each group. But since it is printing it in the Group Footer section, it acts as a group subtotal, not as a running total.