Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    8

    Unanswered: Adding totals for various rows in access

    I've got a membership database for a non-profit, where we track amounts donated over the course of the year. the DB itself was started in Access 97, and i'm not totally sure anymore if it converted over properly and cleanly (do they ever?). But thats another day's work to fix. Anyways, Soon it will be time ot send out statements to our members whom donated over x amount of dollars. The Expression that used to get used worked fine, until we decided to start tracking some things seperatly, and when i added the new columins into the expression it no longer works..

    Expr1: [Active Members]![2009 Donations]+[Active Members]![2009 Ask Letter]+[Active Members]![2009 PayPal Donation]

    We have the 2009 Donations, 2009 Ask Letter and 2009 PayPal Donation coumins that all need to be totaled up, plus probally one more i have yet to look at. Also we might have to do some subtraction ontop of this. Short of exporting it to Excel where i can use =sum is there similar feature in Access to calcuate this, or how do i go about this?

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    There is no easy way in Access like =sum in Excel.

    You could use the Nz function to give a value of 0 if there is no value in the field
    Expr1: nz([Active Members]![2009 Donations],0)+nz([Active Members]![2009 Ask Letter],0)+nz([Active Members]![2009 PayPal Donation],0)

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I was going to make the same suggestion as Poppa. Usually when an addition calculation fails it is because one or more of the components is Null, and

    Anything + Null = Null!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Dec 2009
    Posts
    8
    Is there any way to automatically have Access apply "0"s to null fields my columns?

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In the Table Design View you can set the Default Value for Numeric fields to Zero.

    The only time you wouldn't want to do this would be if the field were ever to be as a Divider in an equation. Division by Zero is illegal and will pop an error.

    For fields used as Dividers, I set no Default Value and then use a variation of the Nz() function:

    Nz(Me.NumericDividerField, 1)


    Used in an equation like

    Me.NumericField/Nz(Me.NumericDividerField, 1)

    if NumericDividerField is left empty (Null) the NumericField will be divided by one, leaving it unchanged.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Dec 2009
    Posts
    8
    Thank you very much, the NZ() command worked, i think we shall get very well acquanted in the future. I've set the default value to 0 for the new column's ive added, and the type to currency (which is what we are tracking), but there is no way other than manually entering the 0 to have it auto populated for null values right?

  7. #7
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You could run an update query to update the relevant columns to 0 where the value is NULL. Before you do this MAKE A COPY OF THE DATA.

Posting Permissions

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