Cumulative sum across the row, not down the column

Cumulative sum across the row, not down the column

How do I find a cumulative sum for records in Access? For example:

 Name col1 col2 col3 col4 col5 Bill 1 2 5 6

I need to have a cumulative sum where applicable, such as:

 Bill 1 3 8 14

I see a problem with blanks or missing values. Please advise.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Both blanks and missing values can be detected simply by testing for a value greater than the empty string. (Microsoft Access is notorious for confusing blanks, empty strings, and nulls, but that's a different rant.) In this particular situation, it's safe to say there cannot be a blank or empty string in those columns, because they must be numeric if you want to perform arithmetic on them. Thus a test for null will work reliably.

In standard databases, you would use a CASE expression and check for a column value that IS NULL. In Microsoft Access, the equivalent is provided by the IIF and ISNULL functions.

 select Name , iif(isnull(col1),null,col1) as sum1 , iif(isnull(col2),null ,iif(isnull(col1),0,col1)+col2) as sum2 , iif(isnull(col3),null ,iif(isnull(col1),0,col1) +iif(isnull(col2),0,col2)+col3) as sum3 , iif(isnull(col4),null ,iif(isnull(col1),0,col1) +iif(isnull(col2),0,col2) +iif(isnull(col3),0,col3)+col4) as sum4 , iif(isnull(col5),null ,iif(isnull(col1),0,col1) +iif(isnull(col2),0,col2) +iif(isnull(col3),0,col3) +iif(isnull(col4),0,col4)+col5) as sum5 from yourtable

This was first published in November 2004

Back to top