Cumulative sum in Access

This question in MS Access. I am designing a small database and I need to calculate a cumulative sum in a query. For example if the query has the following data

1 
2
3

then the result should be

1 
3
6

i.e. each cell equal to itself plus the previous one.

Yes, a cumulative sum can be calculated easily, but it requires that the selected rows have some column that you can ORDER BY.

select T1.sortfield
     , T1.datafield
     , ( select sum(datafield) 
           from yourtable as T2
          where T2.sortfield
             <= T1.sortfield ) as cumsum
  from yourtable as T1
order by
       T1.sortfield

For each row, the subselect calculates the sum of all datafield values for all rows where the sortfield is less than or equal to the sortfield of the row in question.

In your example, with only one column in the table, the sortfield is also the datafield, so your query would be

select T1.datafield
     , ( select sum(datafield) 
           from yourtable as T2
          where T2.datafield
             <= T1.datafield ) as cumsum
  from yourtable as T1
order by
       T1.datafield

Although at first it might appear that "the sum of all datafield values for all [previous] rows" and "equal to itself plus the previous one" aren't the same result, they are -- assuming you meant "equal to itself plus the previous total." I'm pretty sure this is what you wanted, because otherwise, if you wanted "equal to itself plus the previous value," then your sample results would have been

1 
3
5

which would be some kind of Fibonacci number and not a cumulative total.

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.