Learn how to a build a cloud-first strategyRegister Now


Sum data in multiple fields and store number in another field

Posted on 2007-08-02
Medium Priority
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
  • 3
  • 2
LVL 13

Expert Comment

ID: 19619368
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

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


Author Comment

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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 13

Accepted Solution

Ryan earned 1000 total points
ID: 19619570
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

ID: 19619755
Worked like a champ!!!!
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19621994
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

810 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