Sum data in multiple fields and store number in another field

Posted on 2007-08-02
Last Modified: 2012-06-27
I am trying to sum the data in multiple fields; d01, d02, d03....d31. I need to sum them per record and place the sum in another field [Aug07].
I want (or need) to do this by VBA code so when I press a button it will sum and put the numbers in the [Aug07] field w/o stopping until it gets all records.

PO           D01     D02     D03     D04 ...... Aug07
PO123RT 10         3         7         25           45

Question by:edrz01
    LVL 13

    Expert Comment

    You can do it without vba in a report or form.
    If you're grouping the data, put a text box in that groups header or footer. Set the countrol source to =sum(<name of field to add>)+sum(<name of 2nd field>)

    Do this for all the fields you want added.
    LVL 13

    Expert Comment

    Sorry misread. If its per record. Put the textbox in the same detail and set its control source =d01+d02+d03...


    Author Comment

    Thanks for the response. I tried it but I do need the summed data to post back to a field in the table. Suggestions?
    LVL 13

    Accepted Solution

    Its not recommended to store calculated fields into tables (you can calculate as necessary).

    If you must, you can do an update query to add them up for you, it'll be much faster than using recordsets.

    "UPDATE [Name Of Table] SET [AUG07] = [d1]+[d2]+[d3]+[d4]"

    Then just run the query as necessary.

    Author Comment

    Worked like a champ!!!!
    LVL 92

    Expert Comment

    by:Patrick Matthews
    MrBullwinkle said:
    >>Its not recommended to store calculated fields into tables (you can calculate as necessary).

    Absolutely!  And taking it a step further, I would suggest that rather than having D01, D02, D03, etc
    as fields on a record, what you really should be doing is normalizing the table and making one
    record for what is now a field.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now