[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Updating a Running Total field after entering a new Item

Posted on 2011-10-26
Medium Priority
Last Modified: 2013-11-27
Hi Experts,
I have a form where the user will enter a new record, which contains among other things a field for TotalLBS, and a field for LotNumber.   This form is based on a table called tblSHInv

I have a table that holds infomation about LOTS called tblLots (LotNumb, StartDate, EndDate, TotalLotLBS)

When the user saves the record, I would like to take the TotalLBS from the new record in tblSHInv and add it to the TotalLotLBS field of the current LotNumb in tblLots.  

My question is, is there a better way to update the TotalLotLBS value other than building a query and then running that query when they save the record?

Also wondering if I'm missing something in keeping a running total of an item in a field.... or should I build some sort of transaction table and total from that.   Looking for correct method.

Thanks for any help.
Question by:TechGuise9
  • 6
  • 4
LVL 31

Expert Comment

by:Helen Feddema
ID: 37031768
Running totals are a feature of reports, but not of forms, so you will need to do something with a query or SQL to get a running total on a form.  One way to do this is to put a little subform on the form, to display the calculated running total.  Requery it as needed, say from AfterUpdate of a relevant field, or AfterInsert of the form record.

Author Comment

ID: 37031840
I guess I didn't explain it well enough.   I am needing to update the value of a field in another table based on the record just entered.

TotalLotLBS = (TotalLBS + TotalLotLBS)

Getting a total on a FORM is not an issue.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37031869
Typically "calculated" data is not stored.

...Can you explain the need for this...?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 37031914
Hi Boag,
When the user first opens the FORM where they enter a new inventory item, I've built a routine that checks the accumulated total of the current Lot#  (tblLots, field = TotalLotLBS)

If the TotalLotLBS are over a prescribed amount, it prompts the user to create a new Lot#.  

I'm sure I've not been following good practice in the past with things like inventories when it comes to running totals.... but this is the way I've been doing it.   Sugestions?

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37031942
...Sorry, I'll explain.

If you store calculated values, you set yourself up for having to create systems to update these values continuously if any precedent values change.

Typically a query is created and the value is calculated there.
This query can, in turn, be used just like a static table, only no "update" system need be created.

So again, can you provide a bit more info on this system?


Author Comment

ID: 37032029
The system is to track new products in a food manufacturing facility.   Each new product coming off the line assigned to a Lot# (used in case of a re-call).  

So the lbs of each new inventory unit is added to the associated Lot#.  

So if I am understanding you correctly, I should not store the accumulated Lot lbs but instead look to a query that provides a sum of the lbs.
And if I'm still understanding you correctly, if I were using the same concept with a "quantity on hand" scenario for an inventory item, I would use a query to add all the credits to inventory, subtract all of the debits from inventory and that (using a good beginning qty) should give me a good QOH total.

I guess the reason I never considered doing that is that is seems like these queries are going to be working harder and harder as the years go by.   Meaning the # of records they will be summing will get longer and longer.


Author Comment

ID: 37032098
Hi Jeff,
I've just took a look at the Northwind database queries.   I guess I was understanding you correctly.  

For some reason the concept is hard for my brain to trust, but I guess as long as we know all that came in and went out, it's foolproof.

Now I guess I need to know how to pass some criteria to a query using VBA (to get the current Lot#)
Should I open another question?
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 37033489
Actually you provided just as good of an explanation as I could have...

Every situation is different, ...that why most Experts will say "In General you should..." (or something similar), and not speak in absolutes (you should never...")

In a nutshell "Stored" calculations will require systems to keep them up to date for all data entry scenarios, whereas a query will not.
(ex. You create a system to store the calculated value in a table when the record is "created" in the form.
Great, ...but what if a person "modifies" a record?
What if they don't use the form, but instead use the table...
Or what happens if you have to bypass the form for testing purposes...
Your stored calculation may no longer be valid in theses scenarios...

This being said, there are times when storing calculations might be better
*IF* the calculation turns out to take and excruciatingly long time to complete (the key word is IF, because only time and other factors will contribute to this)
Also if the "Calculation" is fixed.
An example would be a Death Database, where instead of calculating the Age at death, it is stored. (The date of death and the date of birth will always be the same)

On the other hand a persons "Current age" should be done in a query.
Make sense?

I was difficult for me to understand at first as well, but a Query can be used in almost every situation that a table can, with the benefit of doing calculations.




Author Comment

ID: 37033759
Yes, makes a lot of sense.   Thanks for taking the time.

And.... You answered my question of "what if they need Period Ending Balances?"   (i.e things that will never change)

Thanks again!
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37034082
Just to be clear, ...as I stated, each situation is different...

Let's say that the Age at death is stored (because neither the DOB nor the DOD dates will ever change)
Now let's also say that the DOB was entered incorrectly...
Wen the DOB is updated you need an *Iron-Clad* system to Re-update and store the value, under all data entry scenarios...

Again, just FYI.



Author Comment

ID: 37034704
Understand.  thanks

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

872 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