Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Row values SUM (Access2000-03)

    Hello!
    What would be the way to add values per record in table where first column is unique IDs and 4 other columns are numbers that I need summary of.
    ID 123 Val1=3 Val2=4 Val3=5 Val4=NULL Sum=12

    So far using + does calculate only those columns where all columns have all values.
    If one value in a row is missing - it would not calculate other 3 that are present.

    I built the table by Nake Table query and than I am taking that table to SUM rows values.
    Is it possible?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Row values SUM (Access2000-03)

    Use this expression in a query based on the table:

    RowSum: Nz([Val1],0) + Nz([Val2],0) + Nz([Val3],0) + Nz([Val4],0)

    If you want to calculate the sum in the control source of a text box on a form or report, use

    =Nz([Val1],0) + Nz([Val2],0) + Nz([Val3],0) + Nz([Val4],0)

    Nz(a, returns a if a is not null (blank), and b if a is null.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row values SUM (Access2000-03)

    Hmmm...brilliant as normally would be any of your suggestion! THANKS! :-)

Posting Permissions

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