Why do people do this? (1 Viewer)

[neileg]

Reading a post on here has prompted me to post this.

It baffles me why lots of users chuck in a spurious Sum() in their expressions. One of my colleagues uses this form all the time in Excel, too.

If you write an expression A+B+C this calculates the sum. If you embrace this with Sum(), i.e. Sum(A+B+C) you are summing what is inside the brackets, which has already been evaluated as a single number, since Access (and Excel) start from the innermost calculation.

Now most of the time, this is simply a redundant operation and does not affect the value, but it can in certain circumstances result in an unitended outcome. It is therefore better practice not to do this.

The proper syntax for the Sum() function is of course Sum(A,B,C).

 

[Oldsoftboss]

Don't use the Sum much in access but in excel it replaces the need to include all the cells in your caculation eg:
if in cell A1 you put =SUM(A2:A100) then A1 would read the total of the cells A2 to A100 ( A1 + A2 + A3 etc)
Dave

 

[neileg]

Yeah, Dave I know that. It's just the combination of Sum() and the A+B+C... that does my head in!

 

[Oldsoftboss]

Think of our understanding as others misunderstanding.
Once enlightened, they should realize the sense in what you are trying to covey
Dave

 

[mission2java_78]

huh?

 

[Pat Hartman]

Actually the Sum() functions in Excel and Access are quite different despite their identical names.

The Sum() function in Excel works across a set of columns or down a set of rows and takes a number of arguments. You use an argument to specify a range of cells or a series of discrete cell references.

In Access Sum() ONLY sums an expression for a set of rows. The argument does NOT specify a range of fields (which are not cells no matter how much a table in datasheet view looks like a spreadsheet). When you see Sum(A + B + C) in Access, the instruction is telling access to add three fields and sum the result for All rows returned by the recordset. A, B, and C are column names and instead of adding them together you might do anything valid with them. Sum(A/B) divides A by B and sums the results. The argument (there is ONLY ONE argument) can be any valid expression. A+B+C results in a single value which is why it is acceptable as an argument. Access doesn't support specifying a range of column names because field order has no meaning in a table and repeating groups violate first normal form anyway. It does not support specifying a range of rows because that would imply that a resultset could include rows that are summaries as well as rows that are details which is NOT possible (unless you union a summary and detail recordset together).

 

[Vassago]

Ditto Pat, I think that kind of clarifies it. I was going to say the same.

 

[bradcccs]

Aww, I was so looking forward to your explanation Vass

Looks like I will have to re-read Pat's.