Acess 2007 sum multiple columns
Hi,
I am looking for a function in Access that can sum a range of columns. In my table, I have 12 months of sales data in crosstab format. In other words, the table look like this:
Part# Jan Feb......Dec
1234 12 32...... 34
What I want to achieve is sum on running bases. For example, for the month Mar., sum the sales for Jan and Feb, while for the month of July, sum the sales from Jan thru Jun. There are business reasons why I need to do this way. Question is how to make the Sum function work in Access. I know in Excel you can simply put a range, Sum(A1:A12). That does not seem to work in Access.
Appreciate any ideas!
I am looking for a function in Access that can sum a range of columns. In my table, I have 12 months of sales data in crosstab format. In other words, the table look like this:
Part# Jan Feb......Dec
1234 12 32...... 34
What I want to achieve is sum on running bases. For example, for the month Mar., sum the sales for Jan and Feb, while for the month of July, sum the sales from Jan thru Jun. There are business reasons why I need to do this way. Question is how to make the Sum function work in Access. I know in Excel you can simply put a range, Sum(A1:A12). That does not seem to work in Access.
Appreciate any ideas!
You could edit the SQL query of the view and do something like this:
SELECT partnumber, SUM(jan + feb + dec) AS total FROM tablename
SELECT partnumber, SUM(jan + feb + dec) AS total FROM tablename
ASKER
For the sum(jab+feb+dec), it will give me three months total or 12?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It will add the values of the fields, not count the fields.
I you table will have "normal" data structure:
Part# Month(Date) Value
1234 1 25
1234 2 13
1234 5 17
here is example:
http://www.datapigtechnologies.com/flashfiles/runningsum.html
and many other examples:
http://www.datapigtechnologies.com/AccessMain.htm
Part# Month(Date) Value
1234 1 25
1234 2 13
1234 5 17
here is example:
http://www.datapigtechnologies.com/flashfiles/runningsum.html
and many other examples:
http://www.datapigtechnologies.com/AccessMain.htm
ASKER
Good to know there is no shortcut to do this in Access. And thanks for the extra help on pssible null values.
I am aware of the data normalization situation. But the data come from another source which I have no control. So I have to take them in the format available.
I am aware of the data normalization situation. But the data come from another source which I have no control. So I have to take them in the format available.
Glad to see you got an answer!For the future, you might want to consider the approach taken in my article https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1775-Computing-row-wise-aggregations-in-Access.htmlIt involves adding a UDF :)Patrick
Patrick,
I like the function. I've got a set of similar, single purpose functions, but like the way you have grouped this together into a single package. I also like the way you have incorporated the ability to pass an array as one of the "values" in the parameter array.
I like the function. I've got a set of similar, single purpose functions, but like the way you have grouped this together into a single package. I also like the way you have incorporated the ability to pass an array as one of the "values" in the parameter array.
ASKER