Adding multiple columns from a table into one report box.

**arcanecantrips** · Nov 9th, 2012, 11:51 AM

I've created a report with some totals of sales for my employees, there are 4 different types of sales. The report shows each total individually but when I try to get the report to add the all together it just shows blanks. here is the formula i'm using:

Code:

=sum(([sales1])+sum([sales2])+sum([sales3])+sum([sales4]))

Seems to me like it should work, but i'm at a loss.

Thanks!

**andiemac** · Nov 10th, 2012, 10:50 AM

First to check, it looks like you have a parenthesis out of place:

=(sum([sales1])+sum([sales2])+sum([sales3])+sum([sales4]))

or

=sum([sales1])+sum([sales2])+sum([sales3])+sum([sales4])

**Oeldere** · Nov 10th, 2012, 10:55 AM

If your data are condesed, maybe you can also use a pivot table for your question.

**arcanecantrips** · Nov 12th, 2012, 05:56 PM

I think I figured the issue out, there were null values in my table and I suppose the report wasn't reading them as 0. So, rather than using the nz function (which I can't figure out any way...), i just entered 0's into all of the fields, they are now adding up fine in my report. Thanks for all the help!!

I do however have a secondary question, I have another table that I need to enter a lot of the same number into. In Excel I can highlight a certain amount of cells enter the number then hit Crtl+Enter and it will enter the same number in all the cells selected, is there a similar function in Access?

I've changed the Default Value of my fields for the future but I'm needing to enter the number into past fields.

Thanks!

**andiemac** · Nov 12th, 2012, 06:45 PM

Are all those records Null and do you want all of them that are Null to be changed? Run an update query against it - changing the table and field names as neccessary.

UPDATE yourtablename SET yourtablename.yourfieldname = 0 WHERE (((yourtablename.yourfieldname) Is Null));

**strive4peace** · Nov 13th, 2012, 10:43 PM

> "the nz function (which I can't figure out any way...)"

NZ is simply a function that returns a value if there is no value. All that is required is the first argument, what you want the value of. If there is no value and Access decides this is a number it will return 0 (zero). If there is no value and Access decides this is a text it will return an empty string, ""

I prefer to specify the optional second argument. I often use NZ to find out if a date is in a particular range and specify the optional parameter to either be an early historical date or something wildly in the future

nz(textexpression, "")
nz(numericexpression,0)

> "changed the Default Value of my fields for the future "

I wouldn't rely on this ... use NZ. Users can press DELETE and wipe it out!

**strive4peace** · Nov 13th, 2012, 10:53 PM

=sum(([sales1])+sum([sales2])+sum([sales3])+sum([sales4]))

using NZ, this would be:

Code:

= nz([sales1],0) + nz([sales2],0) + nz([sales3],0) + nz([sales4],0)

don't use SUM if you are putting this calculation on the same level. If, however, the equation is in a header or footer section, where you truly do want to aggregate, then preface the whole thing with SUM

Code:

= sum( nz([sales1],0)+ nz([sales2],0) +nz([sales3],0) +nz([sales4],0) )

It seems to me that your data is not normalized ...

> "4 different types of sales"

instead of entering the sale in a different column, it might be better to have a SaleTypes table and store a SaleTypeID with the amount. If you can get Amount in ONE field, your reporting and calculating will be much easier! So if there are 2 sale types for a transaction, there would be 2 records.