I found a *MUCH* better solution to my own suggested approach and it does 
not require changing table structures! Again, using the nwind.mdb 
(Northwind) database, this SQL statement will get a running total of the 
Freight charges in the "Orders" table. I found this by doing a search on 
Google Groups:

go to:
                 http://groups.google.com/groups

search for:     running total sql access

The solution was provided by Joe Fallon.

SELECT (SELECT COUNT(OrderID) FROM Orders AS temp
         WHERE temp.OrderID <= Orders.OrderID)
         AS Recno, Orders.OrderID, Orders.Freight,
        (SELECT Sum(Freight) FROM Orders AS temp
         WHERE temp.OrderID <= Orders.OrderID) AS RunningTotal
FROM Orders
ORDER BY Orders.OrderID

Regards,
-Toby


>You could "cheat" by adding a column to the table called "RunningTotal" of 
>type "Currency" and using a disconnected recordset so nothing is actually 
>changed in the base table. When you have the recordset you could then do 
>the calculation. I added a column called "RunningTotal" to the "Order 
>Details" table of the nwind.mdb (Northwind) and did this:
>
>
>
>Dim sSQL As String
>Dim oRs As ADODB.Recordset
>Set oRs = New ADODB.Recordset
>sSQL = "SELECT UnitPrice, Quantity, Discount,  [RunningTotal] " & _
>        "FROM [Order Details]"
>
>
>With oRs
>  .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>                      "Data Source=c:\nwind.mdb;" & _
>                      "Persist Security Info=False"
>
>  .CursorLocation = adUseClient
>  .CursorType = adOpenKeyset
>  .Open sSQL, , adOpenKeyset, adLockBatchOptimistic, adCmdText
>
>' Disconnect so no changes occur:
>   Set .ActiveConnection = Nothing
>
>  Dim nRunTot As Currency
>  Do Until .EOF
>   nRunTot = nRunTot + oRs("UnitPrice").Value * _
>                     oRs("Quantity").Value * _
>                     (1 - oRs("Discount").Value)
>
>    oRs("RunningTotal").Value = nRunTot
>    .MoveNext
>  Loop
>  .MoveFirst
>
>End With

>From: "Farhan" <friend@i...>
>Date: Thu, 27 Dec 2001 01:20:19
>
>
>I am printing detail of invoice. What I need is a running total in the
>DETAILS section of the report like:
>
>Date        Invoice    Amount      Tax     Total     RunningTotal
>11/25/2001  12345      1000.00   50.00   1050.00     1050.00
>11/26/2001  15524      1500.00  100.00   1600.00     2650.00
>and so on ...
>
>i tried DataEnvironment to have an extra field, but somehow i cant update
>that field before showing the report.
>
>I also do not know how to assign ADODB Recordset to Report datasource. And
>even if i do that, how do i design the report based on that recordset?
>
>I would be very much thankful for any help.
>
>Farhan