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.

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

Dig Deeper on SQL Server Database Modeling and Design

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.



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: