Cumulative totals in MS Access

[terry3948]

I am trying to establish a column on a monthly report that will show the accumulated totals for each row by counting from the previous months to the present month without projecting into future months. Also the accumulated data should only count the current year. The basic concept of the simple =Sum([data]) works for the current month, however I am obviously missing a piece of the formula to get the accumulation for the previous months. Anyone had any experience with this? Any help appreciated.

 

[dhookom]

Apparently you want to pull details from a current time period with totals from a previous time period. If this is the case, you might want to use a union query. For instance, if you were reporting the Freight by CustomerID from the Northwind sample database and wanted to total the Freight prior to 1/1/1997 and display the details between 1/1/1997 and 1/31/1997:

SELECT Orders.CustomerID, Null as OrderDate, Sum(Orders.Freight) as SumPrevFreight
FROM Orders
WHERE OrderDate < #1/1/1997#
GROUP BY CustomerID
UNION  ALL
SELECT Orders.CustomerID, Orders.OrderDate, Orders.Freight
FROM Orders
WHERE OrderDate Between #1/1/1997# And #1/31/1997#;

Duane
Hook'D on Access
MS Access MVP