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
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.