using the sum of two fields to get a total sum 1

[southernweyr]

I have tried to calculate the sum of two fields with little success. I have been able to combine the numbers in two fields by using

SELECT [hours data].hours AS hours, [hours data].[total hours], Sum(Nz([hours],0)+Nz([total hours],0)) AS total
FROM [hours data]
GROUP BY [hours data].hours, [hours data].[total hours];

or total: Nz([hours],0)+Nz([total hours],0)
They both just put all of the numbers from the two fields into the field "total." Like this
hours | total hours | total
0 182 182
2 0 2
3 0 3
7 0 7
Trying total:[hours]+[total hours]
ends up having all of the numbers from [hours] but nothing from [total hours]
What I want is 182+2+3+7=194 or 182+12=194
hours | total hours | total
182 194
2
3
7

 

[GingerR]

You won't get it to look exactly like that in a query. Formatting data in particular ways is best left for reports, not queries.

You can get just the total with this:

SELECT Sum(nz([Hours],0)+nz([Total Hours],0)) AS Total
FROM [Hours Data];

Or you can get sorta what you are looking for with this, the issue being that it repeats the total on every row:

SELECT [Hours Data].Hours, [Hours Data].[Total Hours], nz(DSum("Hours","Hours Data"),0)+nz(DSum("[Total Hours]","Hours Data")) AS Total
FROM [Hours Data]
GROUP BY [Hours Data].Hours, [Hours Data].[Total Hours], nz(DSum("Hours","Hours Data"),0)+nz(DSum("[Total Hours]","Hours Data"));

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[southernweyr]

Thank you very much for your reply. Both of your suggestions worked great. The first one is what I wanted for what I am doing.