Summing multiple columns in the same query...

[ewomack]

Hello! I'm new here, and I'm back into Access after a few years of not using it.

What I'm trying to do seems simple, but I can't seem to get the sytax down.

I have 6 columns that have dollar figures in them. I want to get the totals for all 6 columns to show in one field. I'm using the sum([field]) to total the individual columns, but I can't seem to get all of them together in one sum. Is this possible?

Thanks!!

 

[Brian1960]

The short answer is "Yes" but probably not very helpful!!!

You have one query within another. But I need to check the actual sytax.

 

[ewomack]

I should also mention that I'm working with an inherited Access DB. The columns I'm trying to sum should've been normalized out into their own table. But so goes life with Access.

And I'm finding that "Sum([field 1] + [field 2] + [field 3]) As Total" gives strange results.

 

[Brian1960]

Try this

As a quick suggestion Sum([Field1]) + Sum([Field2]) + Sum([Field3]) as GrandTotal from etc etc

I think Total may be a reserved word. The grouping is quite important as well.

 

[Brian1960]

Table with field1, field2, field3.

SELECT Sum([field1]) AS Expr1, Sum([field2]) AS Expr2, Sum([field3]) AS Expr3, Sum([field1])+Sum([field2])+Sum([field3]) AS Expr4
FROM TblTest1;

This works fine.

 

[neileg]

SELECT Sum([field1]) AS Expr1, Sum([field2]) AS Expr2, Sum([field3]) AS Expr3, Sum([field1]+[field2]+[field3]) AS Expr4
FROM TblTest1;

Should work, too.

Beware nulls, however as this will stop the calculations. If you wrap each field in Nz() this will treat null fields as a zero.

 

[tehNellie]

I know this is quite an old thread, but awise fwom the gwave!

I've been having a play with trying to generate some SLA reports of how long it took certain things to be actioned. In my output query I have a DCOUNT of records within SLA and a DCOUNT of records outside of the SLA. I wanted to add a % within SLA and went for the simple formula:
([records inside SLA]*100) / ([records inside SLA] + [records outside SLA])

Which naturally gave me totall screwy results because + isn't adding the two values together, it combines them in one long string. Using the SUM examples above also didn't work although I'm not entire sure why although I suspect it's a result of the output being a DCOUNT.

Anyway to cut a long story short, to get the correct value I used
(cint([records inside SLA]) + cint([records outside SLA])) to perform the addition.

 

[ghudson]

I had to Cdbl instead of Cint to get mine to work plus the Nz function.

Nz(Cdbl(Closed]),0)/(Nz(Cdbl([Closed]),0)+Nz(Cdbl([Open]),0)+Nz(Cdbl([Retired]),0))

 

[vbaInet]

What about Clng()?

 

[csnodgrass]

If you are having summing multiple columns, or just using math functions, check the columns for NULL or missing values. In those cases, just write iif statements, or nesting iif statements to account for those. I would account for the NULLs prior to the summary query, whenever possible.