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

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top