Adding multiple columns from a table into one report box.

[arcanecantrips]

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:

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

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

Thanks!

 

[andiemac]

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]

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

 

[arcanecantrips]

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?</SPAN>

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]

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]

> "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]

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

using NZ, this would be:

= 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

= 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.

 

[arcanecantrips]

Honestly, this helps more than you know. It also solves a couple of other problems. Thank you so much!

 

[strive4peace]

you're welcome happy to help