Results 1 to 9 of 9

Adding multiple columns from a table into one report box.

This is a discussion on Adding multiple columns from a table into one report box. within the Microsoft Access forums, part of the Question Forums category; I've created a report with some totals of sales for my employees, there are 4 different types of sales. The ...

  1. #1
    New Member
    Join Date
    Aug 2012
    Location
    Utah
    Posts
    24

    Default Adding multiple columns from a table into one report box.

    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!

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Washington
    Posts
    222

    Default Re: Adding multiple columns from a table into one report box.

    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])
    Life is too short to keep repeating the same steps over and over. Automate everything you can with VBA.

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Location
    The Netherlands
    Posts
    1,220

    Default Re: Adding multiple columns from a table into one report box.

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

  4. #4
    New Member
    Join Date
    Aug 2012
    Location
    Utah
    Posts
    24

    Default Re: Adding multiple columns from a table into one report box.

    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!

  5. #5
    Board Regular
    Join Date
    Oct 2009
    Location
    Washington
    Posts
    222

    Default Re: Adding multiple columns from a table into one report box.

    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));
    Life is too short to keep repeating the same steps over and over. Automate everything you can with VBA.

  6. #6
    Board Regular strive4peace's Avatar
    Join Date
    Jul 2009
    Location
    Colorado
    Posts
    532

    Default Re: Adding multiple columns from a table into one report box.

    > "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!
    Last edited by strive4peace; Nov 13th, 2012 at 10:46 PM.
    Warm Regards,
    Crystal
    Microsoft MVP, Access

    Remote Programming and Training ~ let's connect!
    http://msaccessgurus.com/

    Document Calculated Fields: Access to Excel (cc)
    http://www.youtube.com/watch?v=vS8KfHU6L90

    4-minute demo of cool SQL + free VBA download

    Learn Access By Crystal video playlist
    http://www.youtube.com/playlist?p=PL1B2705CCB40CA4CA

    Access Basics
    http://www.accessmvp.com/strive4peace
    free 100-page book that covers essentials in Access

    *
    have an awesome day
    *

  7. #7
    Board Regular strive4peace's Avatar
    Join Date
    Jul 2009
    Location
    Colorado
    Posts
    532

    Default Re: Adding multiple columns from a table into one report box.

    =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.
    Warm Regards,
    Crystal
    Microsoft MVP, Access

    Remote Programming and Training ~ let's connect!
    http://msaccessgurus.com/

    Document Calculated Fields: Access to Excel (cc)
    http://www.youtube.com/watch?v=vS8KfHU6L90

    4-minute demo of cool SQL + free VBA download

    Learn Access By Crystal video playlist
    http://www.youtube.com/playlist?p=PL1B2705CCB40CA4CA

    Access Basics
    http://www.accessmvp.com/strive4peace
    free 100-page book that covers essentials in Access

    *
    have an awesome day
    *

  8. #8
    New Member
    Join Date
    Aug 2012
    Location
    Utah
    Posts
    24

    Default Re: Adding multiple columns from a table into one report box.

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

  9. #9
    Board Regular strive4peace's Avatar
    Join Date
    Jul 2009
    Location
    Colorado
    Posts
    532

    Default Re: Adding multiple columns from a table into one report box.

    you're welcome happy to help
    Warm Regards,
    Crystal
    Microsoft MVP, Access

    Remote Programming and Training ~ let's connect!
    http://msaccessgurus.com/

    Document Calculated Fields: Access to Excel (cc)
    http://www.youtube.com/watch?v=vS8KfHU6L90

    4-minute demo of cool SQL + free VBA download

    Learn Access By Crystal video playlist
    http://www.youtube.com/playlist?p=PL1B2705CCB40CA4CA

    Access Basics
    http://www.accessmvp.com/strive4peace
    free 100-page book that covers essentials in Access

    *
    have an awesome day
    *

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com