Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: