sum function on forms in access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using access 2003. i've created a form for recording monthly donations
which includes 12 field for Jan - Dec. I want a box or field for
'year-to-date' to have a running total of donations for each donor. it seems
easy enough -....
my thought was =sum([jan]+[feb]+[mar]...) so forth.... this doesn't seem to
work. where am i going wrong?
ps - I am a newbee - bear that in mind - THANKS!

Newbee.
 
I'm using access 2003. i've created a form for recording monthly donations
which includes 12 field for Jan - Dec.
Click to expand...

Then your table is incorrectly designed. What will you do next
January? Scrap your database and start over?

You have a one (donor) to many (donations) relationship. The proper
way to manage a one to many relationship is with TWO tables, e.g.:

Donors
DonorID
LastName
FirstName
<other bio information>

Donations
DonorID <same datatype as DonorID, Long Integer if that's an
Autonumber>
DonationDate
Amount

You'ld add a new RECORD (using a Subform) into Donations for each
donation from the donor.
I want a box or field for
'year-to-date' to have a running total of donations for each donor. it seems
easy enough -....
my thought was =sum([jan]+[feb]+[mar]...) so forth.... this doesn't seem to
work. where am i going wrong?
ps - I am a newbee - bear that in mind - THANKS!
Click to expand...

The Sum() function sums values in the SAME field across MULTIPLE
records. If you just want to add up the values in your non-normalized
monthname fields, you need to allow for the possibility that one might
be blank (NULL); any expression involving NULL returns NULL. The
getaround is to use the NZ (Null To Zero) function:

=NZ([Jan]) + NZ([Feb]) + NZ([Mar]) + ...

With the normalized table structure, with one record per donation, you
can indeed use the Sum() function; in your subform's Footer put a
textbox with COntrol Source

=Sum([Amount])


John W. Vinson[MVP]
 
Back
Top