Summing multiple columns in the same query... (1 Viewer)

ewomack

Registered User.
Local time
Today, 09:00
Joined
Oct 27, 2005
Messages
11
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!! :D
 

Brian1960

Brian1960
Local time
Today, 16:00
Joined
Aug 13, 2004
Messages
141
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

Registered User.
Local time
Today, 09:00
Joined
Oct 27, 2005
Messages
11
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

Brian1960
Local time
Today, 16:00
Joined
Aug 13, 2004
Messages
141
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

Brian1960
Local time
Today, 16:00
Joined
Aug 13, 2004
Messages
141
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

AWF VIP
Local time
Today, 16:00
Joined
Dec 4, 2002
Messages
5,975
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

Registered User.
Local time
Today, 16:00
Joined
Apr 3, 2007
Messages
751
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

Registered User.
Local time
Today, 12:00
Joined
Jun 8, 2002
Messages
6,195
I had to Cdbl instead of Cint to get mine to work plus the Nz function.

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

csnodgrass

New member
Local time
Today, 12:00
Joined
Nov 17, 2014
Messages
2
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.
 

Users who are viewing this thread

Top Bottom